SQLServerCentral / Discuss Content Posted by Randy Dyess / Article Discussions / Article Discussions by Author / Managing Jobs Using TSQL / Latest Posts

RE: Managing Jobs Using TSQL

thepunisher9 — Mon, 14 Sep 2009 10:10:56 GMT

Have you updated this routine to work on SQL Server 2005?

RE: Managing Jobs Using TSQL

David Lu — Tue, 06 Apr 2004 17:10:00 GMT

One thing:

why the name of sj and sh data type are sysname? which causes centain problems in datalength() function...

-D

RE: Managing Jobs Using TSQL

David Lu — Tue, 06 Apr 2004 17:07:00 GMT

Excellent. I have the similar situation of handling about 80 above servers. You script of join sj and sh solve most of my delima of checking server by server and consolidate all into one table.

I made following changes:

1. declare @daydiff int select @daydiff = 1 'in case I am only interested in last night job

where cast(left(cast(run_date as char(8)), 4) as int)= datepart(yyyy, getdate()) and cast(substring(cast(run_date as char(8)), 5, 2) as int)= datepart(month,getdate()) and cast(right(cast(run_date as char(8)), 2) as int)= datepart(day, getdate()-@dayDiff) and sh.step_name <> '(Job outcome)'

2. I filtered out the step_name = '(Job outcome)'

3. I union all servers

4. Finally, I create 2 cursor loops to find any existing databases on 80 servers either missing the job set up or with failure status. It works great in either case.

thanks

-D

Managing Jobs Using TSQL

Randy_Dyess — Fri, 21 Mar 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/managingjobsusingtsql.asp