CODEWITHSUNDEEP
sql-servert-sqlopenrowset
Philippe Grondier
Philippe Grondier

Reputation: 11148

T-SQL, OPENROWSET copying tables between databases on the same server

I am developping some code that transfers data between SQL servers. In this phase, all my work is on the same server (local, where I am the owner), but I am already trying to implement the OPENROWSET functionnalities that will be used at production time (where data will be on different servers and where I will have to build queries for the transfers). The following 3 codes are supposed to do the very same thing, but the one making use of OPENROWSET is giving me an error ... Bref, I am stuck! if anyone could help...

3 parts naming: works

USE db1
SELECT * INTO dbo.myTable FROM db2.dbo.myTable

OPENDATASOURCE: works

USE db1
SELECT * INTO dbo.myTable FROM OPENDATASOURCE
    ('SQLOLEDB', 
    'Data Source=127.0.0.1\SQLEXPRESS;Integrated Security=SSPI'
    ).db2.dbo.myTable

OPENROWSET: does not work

USE db1
SELECT * INTO dbo.myTable FROM OPENROWSET
    ('SQLOLEDB', 
     'Trusted_Connection=yes;Server=(Local)', 
     'db2.dbo.myTable')

Where I am getting the following message:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Of course I have tried to use the standard properties of my connection string (as with OPENDATASOURCE) but I was also getting the following error:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

Upvotes: 1

Views: 9410

Answers (1)

Vincent Vancalbergh
Vincent Vancalbergh

Reputation: 3337

For one thing: Your OPENROWSET statement isn't specifying the server instance.

Try

USE db1
SELECT * INTO dbo.myTable FROM OPENROWSET
    ('SQLOLEDB', 
     'Trusted_Connection=yes;Server=127.0.0.1\SQLEXPRESS', 
     'db2.dbo.myTable')

Upvotes: 1

Related Questions