Prev: Insert Trigger on a BulkCopy
Next: Multiple sessions when using ADO
From: Loren Z on 2 Jun 2010 15:37
I have been requested to create a job which will display which SQL Server
Agent jobs have been disabled. I would create the job based a stored
procedure. The stored procedure I am creating would run in msdb and access
the tables sysjobs and sysjobhistory. The only way I can see doing it is
opening a cursor based a query accessing these tables and putting the values
for the job name, job status, and date the job was last run into
varchar(max) string to build a report. I would then send this report to the
applicable parties.

Many SQL programmers advise to use cursors as little as possible. Is there
anyway to accomplish this without using a cursor?


From: Bob Barrows on 2 Jun 2010 18:53
Loren Z wrote:
> I have been requested to create a job which will display which SQL
> Server Agent jobs have been disabled. I would create the job based a
> stored procedure. The stored procedure I am creating would run in
> msdb and access the tables sysjobs and sysjobhistory. The only way I
> can see doing it is opening a cursor based a query accessing these
> tables and putting the values for the job name, job status, and date
> the job was last run into varchar(max) string to build a report. I
> would then send this report to the applicable parties.
>
> Many SQL programmers advise to use cursors as little as possible. Is
> there anyway to accomplish this without using a cursor?

You do realize you can send the result of a sql statment via the @query
argument of the sp_send_dbmail system procedure. No need to loop through
the results and build a string.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 | 
Pages: 1
Prev: Insert Trigger on a BulkCopy
Next: Multiple sessions when using ADO