sql server 跨数据库调用存储过程

9/1/2015来源:SQL技巧人气:1490

sql server 跨数据库调用存储过程

A库存储过程:

create PROCEDURE [dbo].[spAAAForTest](                            @UserName   nvarchar(20) =null ,    @LoginPwd nvarchar(60)  =null)ASBEGIN    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)ASBEGIN   declare @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)ASBEGIN   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 @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',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure

---------------