1 USE [Hik] 2 GO 3 /****** Object: StoredProcedure [dbo].[sp_test] Script Date: 08/21/2015 09:55:21 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 -- Batch submitted through debugger: SQLQuery17.sql|7|0|C:\Users\a\AppData\Local\Temp\~vs6295.sql 9 -- Batch submitted through debugger: SQLQuery14.sql|7|0|C:\Users\a\AppData\Local\Temp\~vsCDAD.sql 10 -- ============================================= 11 -- Author: <Author,,Name> 12 -- Create date: <Create Date,,> 13 -- Description: <Description,,> 14 -- ============================================= 15 ALTER PROCEDURE [dbo].[sp_test] 16 @ServerName VARCHAR(100) 17 AS 18 BEGIN 19 20 SET NOCOUNT ON; 21 22 --如果经常访问或数据量大,建议用链接服务器 23 24 --创建链接服务器 25 exec sp_addlinkedserver @ServerName, ' ', 'SQLOLEDB ', '192.168.1.9 ' ; 26 exec sp_addlinkedsrvlogin @ServerName, 'false ',null, 'sa ', '251314' ; 27 28 --查询示例 29 --declare @tname varchar(20),@num int 30 --set @tname=@ServerName+'.Hik' 31 --declare @sql Nvarchar(1000)=N'select @num=COUNT(*) from '+@tname+'.dbo.Device'; 32 --Exec sp_executesql @sql,N'@num int output',@num output 33 --select @num 34 35 declare @sql Nvarchar(max)=N'select count(*) from '+@ServerName+'.Hik.dbo.Device'; 36 Exec sp_executesql @sql; 37 38 --处理完成后删除链接服务器 39 exec sp_dropserver @ServerName, 'droplogins '; 40 41 END
我也是在网上找到的资源，然后修改了一下，实现了需求的原形，供大家参考。
