CODEWITHSUNDEEP
sql-serversql-server-2008-r2linked-serversqlcmd
user3746424
user3746424

Reputation: 21

using sqlcmd script my linked server queries fail to connect

My stored procedure in SQL Server 2008 R2 runs queries on several local and several remote databases (via linked servers) and works fine when I exec it while in SSMS.

When I run the same exec from a sqlcmd script the linked server connections fail with error...

Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "BLAHBLAH.BLAH.BLAH.ORG".

The sqlcmd invocation is...

sqlcmd -U sa -P myPassword-S localhost 
       -i C:\SS_DB_TRACK\job_run_periodic_nightly.sql 
       -o C:\SS_DB_TRACK\job_run_periodic_nightly.lst

Thanks much if someone can advise me on how to get the sqlcmd script to work.

Jay

Upvotes: 1

Views: 1586

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

The difference you are seeing is most likely due to running in a different security context. The sqlcmd parameters you have show it logging in as sa (usually not a good idea). So if you are seeing different behavior in SSMS then you are probably logging into that same server using Windows Authentication. If you logged into that server, via SSMS, using SQL Server Authentication as sa, then you should see the same error. Along those same lines, if you switch the sqlcmd authentication to be -E for Trusted Connection (i.e. Windows Authentication), then it should work (and it appears that this did, in fact, work). If you prefer that this process not be run under your security context then you need to check the security setup of the Linked Server to see why the two different Logins get different responses from using the Linked Server. Ideally you would use another non-sa Login for the process, possibly even create a Login (that is not a member of the sysadmin server role ;-) to handle this process.

Upvotes: 1

CandiedCode
CandiedCode

Reputation: 604

Instead of "LinkedServerName" try [LinkedServerName]

Upvotes: 0

Related Questions