1

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

Improve this question
2
  • When using SSMS, do you connect to the (local) / default instance with that "sa" login and password, or via Windows Authentication? Commented Jan 8, 2015 at 16:53
  • Thanks, I used the -E for Windows authentication and it works great: sqlcmd -S localhost -E ..... Commented Jan 9, 2015 at 19:09

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Instead of "LinkedServerName" try [LinkedServerName]

Improve this answer

1 Comment

Thanks for feedback, but if you are referring to "BLAHBLAH.BLAH.BLAH.ORG" in my question, then your feedback does not help - "BLAHBLAH.BLAH.BLAH.ORG" is in the error msg not my query code. My query definitely used brackets around the linked server name. Again, everything works fine when invoked from SSMS but fails when invoked from sqlcmd script. I've tried just about everything, running the batch file as administrator, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.