SqlServer 远程执行存储过程或其他非DML语句

 更新时间:2021年3月10日 17:11  
SQL server 数据库调用远程数据库存储过程的实现方法 SqlServer 远程执行存储过程或其他非DML语句

SQL server 数据库调用远程数据库存储过程的实现方法

1、两台数据库 sql1和sql2


2、在sql1上创建存储过程,在sql2上设置定时任务执行存储过程


思路:


1、在sql1上创建job,执行存储过程;在sql2上创建job调用sql1上的job即可。


2、使用dblink去执行,参考:http://blog.csdn.net/kk185800961/article/details/48003293


实现:


1、在sql1上创建存储过程省略


2、在sql1上创建job,并能够使其执行


3、在sql2上创建job,在step界面增加新step,并添加如下语句: EXEC sp_serveroption 'sql1', 'rpc out', 'true'; GO EXECUTE ( ' EXEC MSDB.dbo.sp_start_job N''test_procedure'' ') AT [sql1]


这样既可实现此场景。


原本服务器有1个链接服务器,这是建立发布订阅时创建的,创建命令导出如下(都为同一域中的计算机):

EXEC master.dbo.sp_addlinkedserver
 @server = N'LinkServerName',
 @srvproduct=N'SQL Server' 
GO
 
EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'LinkServerName',
 @useself=N'True',
 @locallogin=NULL,
 @rmtuser=NULL,
 @rmtpassword=NULL
GO


该链接服务器可以进行远程查询,但是调用远程存储过程执行命令时出错:

EXEC [LinkServerName].[DB].DBO.SP_EXECUTESQL N'GRANT SELECT,INSERT,UPDATE,DELETE,REFERENCES ON [AAAAA] TO [username]'


消息 18483,级别 14,状态 1,第 1 行

由于没有在服务器 'LinkServerName' 上将 '' 定义为远程登录名,所以无法连接到该服务器。请确保指定的登录名正确无误。。


查看服务器信息:

SELECT name,product,provider,data_source,is_linked,is_remote_proc_transaction_promotion_enabled FROM sys.servers


 [email protected]=N'SQL Server'  无法远程执行,现在换一种接口类型。
 

EXEC master.dbo.sp_droplinkedsrvlogin [LinkServerName],Null 
EXEC master.dbo.sp_dropserver [LinkServerName]
GO
 
 
EXEC master.dbo.sp_addlinkedserver
 @server = N'LinkServerName',
 @srvproduct=N'',
 @provider='SQLNCLI',
 @datasrc='ServerName or IP'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'LinkServerName',
 @useself=N'True',
 @locallogin=NULL,
 @rmtuser=NULL,
 @rmtpassword=NULL
GO


再执行远程存储过程,错误如下:


消息 7411,级别 16,状态 1,第 1 行

未将服务器 'DB03' 配置为用于 RPC。


开启 RPC 远程过程调用:

EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'rpc out', @optvalue=N'true'


不可在界面上启用RPC,否则报错:


不允许对系统目录进行即席更新。(Microsoft SQL Server , 错误:259)




好了,现在再执行:

EXEC [LinkServerName].[DB].DBO.SP_EXECUTESQL N'GRANT SELECT,INSERT,UPDATE,DELETE,REFERENCES ON [AAAAA] TO [username]'


命令执行成功!


附:几种远程查询

SELECT * FROM [LinkServerName].[DB].dbo.AAAAA
SELECT * FROM OPENQUERY([LinkServerName],'SELECT * FROM DB.dbo.AAAAA')
SELECT * FROM OPENDATASOURCE('SQLNCLI','Data Source=;Integrated Security=SSPI').DB.dbo.AAAAA
SELECT * FROM OPENROWSET('SQLNCLI','Server=ServerName;Trusted_Connection=yes;','SELECT * FROM DB.dbo.AAAAA') AS a;

如何给OPENQUERY传递参数

CREATE PROCEDURE [dbo].[prd_Test]
    @UserId INT
AS
BEGIN
    DECLARE @myUserId VARCHAR(20);
    SET @myUserId = CAST(@UserId AS VARCHAR(20));
    DECLARE @sql VARCHAR(1000);
    SET @sql = 'SELECT * FROM OPENQUERY([192.168.0.252],''SELECT * FROM [User].dbo.func_GetData(' + @myUserId + ')'')';
    EXEC(@sql);
END