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