[轉] sql server 跨數據庫調用存儲過程

A庫存儲過程：

create PROCEDURE [dbo].[spAAAForTest]
(                        @UserName   nvarchar(20) =null ,@LoginPwd nvarchar(60)  =null
)
AS
BEGINselect N'A' AS a, N'B' AS B, N'C' AS C;END

同一臺服務器實例，A，B兩個數據庫， 在B庫的存儲過程中，調用A庫的存儲過程

B庫：

ALTER PROCEDURE [dbo].[spAAAForTest2]
(                        @UserName   nvarchar(20) =null ,@LoginPwd nvarchar(60)  =null
)
AS
BEGINdeclare @sql nvarchar(500);set @sql = N' exec DB_A.dbo.spAAAForTest ';exec  sp_executesql @sqlEND

?

A，B兩個數據庫，不在同一臺服務器實例， 在B庫的存儲過程中，調用A庫的存儲過程

B庫：

ALTER PROCEDURE [dbo].[spAAAForTest2]
(                        @UserName   nvarchar(20) =null ,@LoginPwd   nvarchar(60)  =null
)
AS
BEGINdeclare @sql nvarchar(500);set @sql = N' exec OPENDATASOURCE(''SQLOLEDB'',''Data Source=SERVER-123\MSSQL2008R2;User ID=sa;Password=sa'').DB_A.dbo.spAAAForTest ';exec  sp_executesql @sqlEND

?

?

?

--------------- 在跨服務器調用時，所使用OPENDATASOURCE 遭遇如下信息時

消息 15281，級別 16，狀態 1，第 1 行

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'

because this component is turned off as part of the security configuration for this server.

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries',

see "Surface Area Configuration" in SQL Server Books Online.

?

通過如下方式進行設置：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

?

?

轉自：freeliver54??sql server 跨數據庫調用存儲過程???（侵刪）