2

We have been using this syntax when using OpenRowset to collect data from other SQL Server instances

SELECT a.* 
FROM OPENROWSET('SQLNCLI11', 
'Server=STARDBS1004\T12;UID=user;PWD=pwd;', 
'select * from [MyTable]') as a  

Native Client is deprecated and MS advice is to use latest Microsoft® OLE DB Driver 18 for SQL Server®

I am struggling to find the correct syntax for my OPENROWSET query

This I how think it is supposed to be now:

SELECT a.* 
FROM OPENROWSET('MSOLEDBSQL',
'Data Source=STARDBS1004\T12;Initial Catalog=Staging_DWH;User ID=user;Password=pwd',  
'select * from [MyTable]') AS a

But I get these errors:

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSOLEDBSQL" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Obviously the syntax is wrong, so can anybody help in finding the correct syntax?

1

OLEDB connections using the MSOLEDBSQL driver should use this format:

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=MyServer;Database=tempdb;UID=test;Pwd=Test;'
    , 'SELECT name FROM sys.databases;'
    ) c;

ConnectionStrings.com is invaluable for obtaining valid connection string details.

  • But it is significantly slower than using native client. I have tested a couple of loads, and it is up to 10 times slower that using Native client. Any ideas as to why ? (might be a new question i should open instead) – Peter Tilsted Jun 22 '18 at 8:22
  • Interesting. Yes, that should be a new question. – Max Vernon Jun 22 '18 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.