I have spent an entire day on trying to figure this out and am ready for some help.
I am trying to get the following code to work. (user and pass are left out for obvious reasons) I am running query from SSMS and am looking to take the contents of a store procedure and place it in a temp table. Can you please fix my query so that it works. :)
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=SQL01\SQL2008R2;Database=ExtData;
Uid=xxxx;Pwd=xxxx',
'EXEC sp_MonthlyInventoryStock')
This is the error I get:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid connection string attribute".Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "SQLNCLI10" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "(null)".