sql-server - 在 Openrowset 中执行存储过程以放入临时表中

Question

我花了一整天的时间试图解决这个问题，并准备好寻求帮助。

我正在尝试让以下代码正常工作。 (由于显而易见的原因，用户和通行证被省略)我正在从 SSMS 运行查询，并希望获取存储过程的内容并将其放入临时表中。您能修复我的查询以使其正常工作吗？ :)

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')

这是我得到的错误:

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

先生。雷类德回答了这个问题。看起来连接字符串必须全部在同一行。

您还需要包含“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