Sqlserver使用游标循环，一个sql查询出所有linked server服务器上的某个job信息

结论：如果想要在一台服务器上，cursor查询所有linked server的上的某些信息，把linked server名称[DB123]当成变量时无法使用如下方式

select COUNT(*) from [msdb].[dbo].[sysjobs][DB123]
select COUNT(*) from [DB123].[msdb].[dbo].[sysjobs]
即
'select COUNT(*) from [msdb].[dbo].[sysjobs]'+quotename(@servername)  
'select COUNT(*) from '+quotename(@servername)+'.[msdb].[dbo].[sysjobs]'

只能是如下

EXECUTE ('select COUNT(*) from [msdb].[dbo].[sysjobs] where name like ''DBA - Restore%''') AT [DB123]
'EXECUTE (''select name from [msdb].[dbo].[sysjobs] where name like ''''DBA - Restore%'''''') AT ' + QUOTENAME(@ServerName)

代码如下：

DECLARE @ServerName varchar(200) --声明变量,用户接收循环时的变量

--定义游标
DECLARE RunPerServer CURSOR FOR
SELECT LogicalName FROM [server].List WHERE TypeID = 1 

--打开游标 
OPEN RunPerServer
FETCH NEXT FROM RunPerServer INTO @ServerName --从游标里取出数据赋值到声明的变量中


IF OBJECT_ID('tempdb.dbo.#DBAjobscount1') IS NOT NULL
	DROP TABLE #DBAjobscount1
CREATE TABLE #DBAjobscount1 (
	servername NVARCHAR(128) not NULL,
	job_count INT NULL
	)

IF OBJECT_ID('tempdb.dbo.#DBAjobsname1') IS NOT NULL
	DROP TABLE #DBAjobsname1
CREATE TABLE #DBAjobsname1 (
	servername NVARCHAR(128) not NULL,
	job_name NVARCHAR(128)  NULL
	)


WHILE @@FETCH_STATUS = 0 --返回被FETCH语句执行的最后游标的状态，0表示fetch语句成功，1表示fetch语句失败，2表示被提取的行不存在
BEGIN
	BEGIN TRY
	    declare @jobcount int
		declare @jobname varchar(200) 
		declare @sql1 varchar(8000) = 'EXECUTE (''select COUNT(*) from [msdb].[dbo].[sysjobs] where name like ''''DBA - Restore%'''''') AT ' + QUOTENAME(@ServerName)
		declare @sql2 varchar(8000) = 'EXECUTE (''select name from [msdb].[dbo].[sysjobs] where name like ''''DBA - Restore%'''''') AT ' + QUOTENAME(@ServerName)
		print  @sql1
		print  @sql2	
		IF OBJECT_ID('tempdb.dbo.#DBAjobsname') IS NOT NULL
	    DROP TABLE #DBAjobsname	
		IF OBJECT_ID('tempdb.dbo.#DBAjobscount') IS NOT NULL
	    DROP TABLE #DBAjobscount
		CREATE TABLE #DBAjobscount (job_count INT NULL)		
		CREATE TABLE #DBAjobsname (job_name NVARCHAR(128) NULL)
		insert into #DBAjobscount exec(@sql1) 
		insert into #DBAjobsname exec(@sql2)
		insert into #DBAjobscount1 select @ServerName,job_count from #DBAjobscount
		insert into #DBAjobsname1 select @ServerName,job_name from #DBAjobsname
		DROP TABLE #DBAjobscount
		DROP TABLE #DBAjobsname
		print 'The cursor successfully fetched a restore job'		
	END TRY
	BEGIN CATCH
		PRINT 'Could not get jobs for server: ' + @ServerName + ' Error: ' + ERROR_MESSAGE()
		print @ServerName
	END CATCH
	FETCH NEXT FROM RunPerServer INTO @ServerName
END
CLOSE RunPerServer --关闭游标
DEALLOCATE RunPerServer --撤销游标

select a.servername,b.job_name from #DBAjobscount1 a inner join #DBAjobsname1 b on a.servername=b.servername and a.job_count>0