1.使用游标
View Code
USE msdb GO --???? DECLARE @Result NVARCHAR(4000), @name sys.SYSNAME, @next_scheduled_run_date DATETIME SELECT @Result = N'' DECLARE cursor_mailbody CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR SELECT b.name, a.next_scheduled_run_date FROM msdb.dbo.sysjobactivity a WITH (NOLOCK), ( SELECT MAX(a.session_id) AS session_id, a.job_id, b.name FROM msdb.dbo.sysjobactivity a WITH (NOLOCK), msdb.dbo.sysjobs b WITH (NOLOCK) WHERE a.job_id = b.job_id AND b.enabled = 1 GROUP BY a.job_id, b.name ) b WHERE a.session_id = b.session_id AND a.job_id = b.job_id --AND DATEADD(n, 30, a.next_scheduled_run_date) < GETDATE() OPEN cursor_mailbody FETCH NEXT FROM cursor_mailbody INTO @name, @next_scheduled_run_date WHILE (@@FETCH_STATUS = 0) BEGIN SET @Result = @Result + CHAR(13) + CHAR(10) + CAST(@name AS NVARCHAR(255)) + N',' + ISNULL(CONVERT(CHAR(23), @next_scheduled_run_date, 121), 'NULL') FETCH NEXT FROM cursor_mailbody INTO @name, @next_scheduled_run_date END CLOSE cursor_mailbody DEALLOCATE cursor_mailbody --??xml for path DECLARE @table TABLE ( [name] VARCHAR(20) ) INSERT @table ( [name] ) SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL SELECT 'd' UNION ALL SELECT 'e' SELECT STUFF ( ( SELECT ',' + [name] FROM @table FOR XML PATH('') ), 1,1,'' )