Execute stored procedure within Openrowset to place in temp table

Question

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)".

Answer 1

Mr. Reband answered the question. It looks like the connection string must be all on the same line.

You also need to include 'SET FMTONLY OFF; and have the S.P. set NOCOUNT to ON.

Final answer(the connection string is all on one line):

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'
                ,'SET FMTONLY OFF;SET NOCOUNT ON;EXEC sp_MonthlyInventoryStock')
GO