GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec sp_remote_exec @as_remote_server=N'192.168.x.xx\SQL实例(远程SQL服务器)', @as_login='登录账号(一般是sa)', @as_pass='密码',@as_db_name='数据库名称',@as_exec_sql='select top 1 * from 表名'
CREATE PROCEDURE [dbo].[sp_remote_exec]
@as_remote_server nvarchar(500), --建议加密处理
@as_login nvarchar(30), --建议加密处理
@as_pass nvarchar(30), --建议加密处理
@as_db_name nvarchar(30), --建议加密处理
@as_exec_sql nvarchar(max)
AS
BEGIN
DECLARE @ls_exec_syntax nvarchar(max);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (select count(1) from sys.servers where name = @as_remote_server) <= 0
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = @as_remote_server, @srvproduct=N'SQL Server'
END
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @as_remote_server, @locallogin = N'sa', @useself = N'False', @rmtuser = @as_login, @rmtpassword = @as_pass
select @ls_exec_syntax = N'EXECUTE [' + @as_remote_server + '].[' + @as_db_name + '].[dbo].sp_executesql N''' + @as_exec_sql + ''''
exec sp_executesql @ls_exec_syntax
END
GO