Running a stored procedure in Openrowset to place in a temporary table

Question

I spent the whole day trying to figure it out and am ready for some help.

I am trying to get the following code to work. (user and skip are not taken into account for obvious reasons) I run the request from SSMS, and I want to take the contents of the storage procedure and put it in the temp table. Can you fix my request to make it work. :)

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:

" " 1 1. RECONFIGURE .
" " 1 1. RECONFIGURE .
OLE DB "SQLNCLI10" "(null)" " ".
OLE DB "SQLNCLI10" "(null)" "Invalid connection string attribute".

Msg 7399, 16, 1, 3
OLE DB "SQLNCLI10" "(null)" . .
Msg 7303, 16, 1, 3
OLE DB "SQLNCLI10" "(null)".

Answer 1

. . , .

'SET FMTONLY OFF; S.P. NOCOUNT ON.

( ):

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

Answer 2

.

();

:

SELECT  * 
FROM    OPENROWSET ('SQLNCLI','Server=(local); TRUSTED_CONNECTION=YES;'
  ,'exec master.dbo.sp_who')

SELECT  * 
FROM    OPENROWSET ('SQLNCLI','Server=(local);TRUSTED_CONNECTION=YES;'
 ,'exec master.dbo.sp_who')