SQL Serversql server 跨数据库调用存储过程

A库存储过程:

create PROCEDURE [dbo].[spAAAForTest]
(                        
    @UserName   nvarchar(20) =null ,
    @LoginPwd nvarchar(60)  =null
)
AS
BEGIN
    select 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
BEGIN
   declare @sql nvarchar(500);
   set @sql = N' exec DB_A.dbo.spAAAForTest ';
    exec  sp_executesql @sql

END

A,B两独数据库,不以一如既往台服务器实例,
在B库底贮存过程被,调用A库的积存过程

B库:

ALTER PROCEDURE [dbo].[spAAAForTest2]
(                        
    @UserName   nvarchar(20) =null ,
    @LoginPwd   nvarchar(60)  =null
)
AS
BEGIN
   declare @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 @sql

END

 

 

————— 以越服务器调用时,所下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

 

 


 

相关文章