INF: Job to Monitor SQL Server 7.0 Performance and Activity (286191)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q286191

SUMMARY

This article describes the ActivityTrace7 job that puts blocking and performance data into a trace file for further analysis. Copy the ActivityTrace7 job script from this article into a Query Analyzer window, and then execute the script to create the job. As is, the job is enabled and scheduled to run every minute.

NOTE: This article discusses a DBCC command (DBCC PSS) that is unsupported, and may cause unexpected behavior. Microsoft cannot guarantee that you can solve problems that result from the incorrect use of this DBCC command. Use this DBCC command at your own risk. This DBCC command may not be available in future versions of SQL Server. For a list of the supported DBCC commands, see the "DBCC" topic in the Transact-SQL Reference section of SQL Server Books Online.

MORE INFORMATION

The first time that the job is executed, it creates a file named C:\ActivityTrace7.ini that controls the ActivityTrace7 job. You can edit the ActivityTrace7.ini file by using Notepad.

To start the trace, edit the ActivityTrace7.ini file by changing either or both of the following variables as noted:
  • @blockingcheck = yes @performancecheck = yes
After the change is made, the next execution of the job starts the trace.

To stop the trace, edit the ActivityTrace7.ini file by changing both of the following variables as noted:
  • @blockingcheck = no @performancecheck = no
The next time that the job executes the trace stops.

The results of the trace are written to the C:\ActivityTrace7.trc file. When the trace starts, the original C:\ActivityTrace7.trc file is overwritten, so make sure that you copy the C:\ActivityTrace7.trc file to another location before you restart the trace.

Use SQL Profiler to view the data in the trace file to find blocking or a performance barrier.

For additional information about analyzing the data that is gathered by the trace, click the article numbers below to view the articles in the Microsoft Knowledge Base:

289274 INF: How to View SQL Server 7.0 Activity Data

289276 INF: How to View SQL Server 7.0 Blocking Data

289277 INF: How to View SQL Server 7.0 Performance Data

For additional information about controlling a trace, click the article number below to view the article in the Microsoft Knowledge Base:

289279 INF: How to Monitor SQL Server 7.0 Traces

NOTE: Make sure that you stop the trace by changing the @blockingcheck and @performancecheck parameters to NO in the ActivityTrace7.ini file. If you stop the trace by means of the trace procedure described in the article just referenced (Q289279), the trace stops, but the job continues. The next time the job runs, it reads that at least one of the parameters in the ActivityTrace7.ini file is still set to YES and restarts the trace, overwriting the original ActivityTrace7.trc file.

The ActivityTrace7 job is controlled through the C:\ActivityTrace7.ini file, which contains the following variables:
  • @performancecheck
    If @performance is YES, user event 83 is used to write the sysperfinfo table to the trace file. If @performance is NO, user event 83 is not generated.
  • @blockingcheck
    If @blockingcheck is YES, user event 82 is used to record either "no block" or "blocking." If blocking exits, user event 82 is used to record the sysprocesses and syslockinfo tables to the trace file. The user event 82 is also used to put dbcc pss information into the trace file if blocking exists. If @blockingcheck is NO, that step is skipped and user event 82 is not generated.
  • @tracefile
    Data is written to the trace file specified in this @tracefile variable. Change @tracefile to save the trace to a different drive and file name. Note that a .trc extension is automatically added by the trace.
  • @minMBfree
    When the megabytes of free space on the drive specified in @tracefile is less than the megabytes specified in @minMBfree, the trace is stopped.
  • @stoptime
    Change @stoptime to set a time for the trace to automatically stop.
  • @events
    Change@events to control the events traced.
  • @job_name
    Change the @job_name variable to alter the name of the trace job from ActivityTrace7 to some other job name. Note that if you change the ActivityTrace7 job name, you must also change the name in the ActivityTrace7 job properties.
  • @schedule00seconds
    When set to YES, the job ActivityTrace7 is scheduled to run every minute on the minute as defined by the schedule named schedule00seconds.
  • @schedule15seconds
    When set to YES, the job ActivityTrace7 is scheduled to run every minute at 15 seconds past the minute as defined by the schedule named schedule15seconds.
  • @schedule30seconds
    When set to YES, the job ActivityTrace7 is scheduled to run every minute at 30 seconds past the minute as defined by the schedule named schedule30seconds.
  • @schedule45seconds
    When set to YES, the job ActivityTrace7 is scheduled to run every minute at 45 seconds past the minute as defined by the schedule named schedule45seconds.
Note that if @schedule00seconds, @schedule15seconds, @schedule30seconds, and @schedule45seconds are all set to YES, the job runs every 15 seconds. If @schedule00seconds, @schedule15seconds, @schedule30seconds, and @schedule45seconds are all set to NO, the job does not run at all. In this case, because the job is not running, it cannot detect a change to the ActivityTrace7.ini file; hence, the job must be started manually to allow it to read the ActivityTrace7.ini file for the schedule change.

For additional information about the @events arguments, see the "xp_trace_eventclassrequired (T-SQL)" topic in SQL Server 7.0 Books Online.

For example, consider the following contents of an @traceini file named C:\ActivityTrace7.ini: 
@performancecheck  = no 
@blockingcheck     = no 
@tracefile         = C:\ActivityTrace7 
@minMBfree         = 200 
@stoptime          = 2010-12-31 12:00:00.000 
@events            = 10,11,12,13,16,17,19,33,42,43,55,82,83 
@job_name          = ActivityTrace7 
@schedule00seconds = yes 
@schedule15seconds = no 
@schedule30seconds = no 
@schedule45seconds = no
By setting the variables this way in the ActivityTrace7.ini file, the job ActivityTrace7 (@jobname) runs every minute on the minute (@schedule00seconds = yes). This job is set up to create a trace file, C:\ActivityTrace7.trc (@tracefile), to contain blocking and performance trace activity. However, the trace is not started (@performancecheck = no, @blockingcheck = no). If the trace is started, the following occurs:

The following events are traced (@events):
  • RPC:Completed
  • RPC:Starting
  • SQL:BatchCompleted
  • SQL:BatchStarting
  • Attention
  • ExistingConnection
  • DTCTransaction
  • Exception
  • SP:Starting
  • SP:Completed
  • Hash Warning
  • UserConfigurable:1
  • UserConfigurable:2
The trace stops at 12:00pm on Dec. 31, 2010 (@stoptime).

ActivityTrace7 job script

-- Script generated on 2/19/2001 5:22 PM
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'ActivityTrace7') > 0 
  PRINT N'The job "ActivityTrace7" already exists so will not be replaced.'
ELSE
BEGIN 

  -- Add the job.
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'ActivityTrace7', @owner_login_name = N'sa', @description = N'Trace performance and blocking.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps.
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CreateIniFile', @command = N'-- create C:\ActivityTrace7.ini file
declare @c nvarchar(256), @rc int, @i char(21), @on bit, @job_name sysname, @s sysname
set @i = ''C:\ActivityTrace7.ini''
set @c = ''copy ''+@i+'' C:\ActivityTrace7.bak''
exec @rc = master.dbo.xp_cmdshell @c, no_output
if (@rc = 0) goto finish
set @c = ''echo @blockingcheck     = no > ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @performancecheck  = no >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @tracefile         = C:\ActivityTrace7 >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @minMBfree         = 200 >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @stoptime          = 2010-12-31 12:00:00.000 >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @events            = 10,11,12,13,16,17,19,33,42,43,55,82,83 >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @job_name          = ActivityTrace7 >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @schedule00seconds = yes >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @schedule15seconds = no >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @schedule30seconds = no >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
set @c = ''echo @schedule45seconds = no >> ''+@i
exec master.dbo.xp_cmdshell @c, no_output
finish:
if exists (select * from dbo.sysobjects where id = object_id(N''[_t1]'')
   and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [_t1]
create table _t1 ([c1] nvarchar(512))
exec (''bulk insert _t1 FROM ''''''+@i + '''''''')
if exists (select * from dbo.sysobjects where id = object_id(N''[_t2]'')
   and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [_t2]
create table _t2 ([d1] nchar(1), [on] bit, [s1] nvarchar(128), [o1] nvarchar(245))
if exists (select * from dbo.sysobjects where id = object_id(N''[_s]'')
   and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [_s]
create table _s (id1 int identity, spid smallint, b smallint, d1 bit, ud binary(2540))
select @job_name = cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as sysname) from _t1 where left(c1,3) = ''@jo''
while (select count(*) from _t1 where left(c1,3) = ''@sc'') > 0
 begin
 select top 1 @s = cast(rtrim(ltrim(substring(c1,2,charindex(''='',c1,1)-2))) as sysname) 
 , @on = case upper(cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as varchar(3)))
 when ''YES'' then 1 else 0 end from _t1 where left(c1,3) = ''@sc''
 EXEC msdb.dbo.sp_update_jobschedule @job_name=@job_name, @name = @s, @enabled = @on
 delete _t1 where cast(rtrim(ltrim(substring(c1,2,charindex(''='',c1,1)-2))) as sysname)  = @s
 end', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'CreateTrace', @command = N'-- create trace 7
declare @p varchar(3), @b varchar(3), @tid int, @tfile nvarchar (245), @stop datetime
, @m1 int, @rc int, @c nvarchar(128), @m2 int, @j sysname
, @ets varchar(512), @e int, @es int, @en int, @le int
select @b=cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = ''@pe''
select @p=cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = ''@bl''
select @tfile=cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where left(c1,3) = ''@tr''
select @m1=cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as int) from _t1 where left(c1,3) = ''@mi''
select @stop=cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as datetime) from _t1 where left(c1,3) = ''@st''
select @j=cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as sysname) from _t1 where left(c1,3) = ''@jo''
select @ets=cast(ltrim(rtrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where left(c1,3) = N''@ev''
set @tid = 0
set @c = ''dir '' + left(@tfile,2) + '' | find "bytes free"''
insert into _t1 exec master.dbo.xp_cmdshell @c
select @m2 = cast(replace(substring(c1,charindex(''Dir'',c1)+6,charindex(''bytes free'',c1)
       -(charindex(''Dir'',c1)+6)),'','','''') as int)/1024/1024
  from _t1 where charindex(''bytes free'',c1) > 0
delete _t1 where left([c1],1) != ''@''
insert into _t1 EXEC master.dbo.xp_trace_enumqueuehandles
while (select top 1 count(*) from _t1 where left(c1,1) != ''@'') > 0
 begin
 select top 1 @tid = cast(c1 as int) from _t1 where left(c1,1) != ''@''
 insert _t2 exec master.dbo.xp_trace_getqueuedestination @tid,2
 if (select o1 from _t2) = @tfile + ''.trc'' delete _t1 where left(c1,1) != ''@''
    else delete from _t1 where c1 = cast(@tid as nvarchar(3)) and left(c1,1) != ''@''
 end
if upper(@p) != ''YES'' and upper(@b) != ''YES'' goto stopt
if @stop < getdate() goto stopt
if @m2 <= @m1 goto disable
if @tid != 0 and @m2 > @m1 goto finish
if @tid != 0 goto finish
set @c = ''if exist ''+@tfile+''.trc ''+''del ''+@tfile+''.trc''
exec @rc = master.dbo.xp_cmdshell @c, no_output
if (@rc != 0) goto disable
EXEC master.dbo.xp_trace_addnewqueue 3000, 6000, 95, 90, 129761279, @tid OUTPUT
if (@rc != 0) goto disable
select @es = 1
select @en = charindex('','',@ets,@es)
set @le = len(@ets)
while @en > 0
 begin
 select @e = cast(substring(@ets,@es,@en-@es) as int)
 EXEC master.dbo.xp_trace_seteventclassrequired @tid, @e, 1
 select @es = @en + 1
 select @en = charindex('','',@ets,@es)
 if @en = 0 set @en = @le + 1
 if @es > @le set @en = 0
 end
set @tfile = @tfile + ''.trc''
EXEC master.dbo.xp_trace_setqueuedestination @tid, 2, 1, NULL,@tfile
EXEC master.dbo.xp_trace_startconsumer @tid
goto finish
disable:
exec msdb.dbo.sp_update_job @job_name = @j, @enabled = 0
stopt:
if @tid > 0 EXEC master.dbo.xp_trace_destroyqueue @tid
finish:
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'blocking', @command = N'-- blocking check
declare @ud varbinary(2400),@id int,@spid smallint,@c nvarchar(80),@b varchar(3),@rc int,@ui nvarchar(128)
select @b = cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) =  ''@bl''
if upper(@b) != ''YES'' goto finish
insert into _s select spid, blocked, 0,
cast(spid as binary(2))+cast(kpid as binary(2))+cast(blocked as binary(2))+waittype
+cast(waittime as binary(4))+cast(lastwaittype as binary(64))+cast(waitresource as binary(512))
+cast(dbid as binary(2))+cast(uid as binary(2))+cast(cpu as binary(4))
+cast(physical_io as binary(4))+cast([memusage] as binary(4))+cast(login_time as binary(8))
+cast(last_batch as binary(8)) +cast(ecid as binary(2))+cast(open_tran as binary(2))
+cast(status as binary(60))+cast(sid as binary(86))+cast(hostname as binary(256))
+cast(program_name as binary(256))+cast(hostprocess as binary(16))+cast(cmd as binary(32))
+cast(nt_domain as binary(256))+cast(nt_username as binary(256))+cast(net_address as binary(24))
+cast(net_library as binary(24))+cast(loginame as binary(256))
+cast(l.rsc_text as binary(64))+cast(rsc_bin as binary(16))+cast(rsc_valblk as binary(16))
+cast(rsc_dbid as binary(2))+cast(rsc_indid as binary(2))+cast(rsc_objid as binary(4))
+cast(rsc_type as binary(1))+cast(rsc_flag as binary(1)) +cast(req_mode as binary(1))
+cast(req_status as binary(1))+cast(req_refcnt as binary(2))+cast(req_cryrefcnt as binary(2))
+cast(req_lifetime as binary(4))+cast(req_spid as binary(4))+cast(req_ecid as binary(4))
+cast(req_ownertype as binary(2))
  from master.dbo.sysprocesses p join master.dbo.syslockinfo l on p.spid = l.req_spid
 where (blocked != 0 or waittype != 0x0000)
    or (blocked = 0 and spid in (select blocked from master.dbo.sysprocesses where blocked != 0))
if (select top 1 count(*) from _s) < 1
   EXEC master.dbo.xp_trace_generate_event @event_class = 82, @event_text_data = ''no block''
else
begin
 update _s set d1 = 1 where b = 0 and spid in (select b from _s where b != 0)
 while (select top 1 count(*) from _s where d1 = 1) > 0
  begin
  select top 1 @spid = spid from _s where d1 = 1
  set @c = ''osql -S''+@@servername+'' -Q"dbcc traceon(3604) dbcc pss(0,''
      +cast(@spid as nvarchar(3))+ '')" -o C:\pss.txt -w128''
  exec @rc = master.dbo.xp_cmdshell @c, no_output
  if (@rc = 0) bulk insert _t1 FROM ''C:\pss.txt''
  delete _t1 where left([c1],2) = ''00''
  update _s set d1 = 0 where spid = @spid
  while (select top 1 count(*) from _t1 where left([c1],1) != ''@'') > 0
   begin
   select top 1 @ui = c1 from _t1 where left([c1],1) != ''@''
   exec master.dbo.xp_trace_generate_event @event_class = 82, @event_text_data = @ui
   delete _t1 where left(c1,5) = left(@ui,5)
   end
  set @c = ''del C:\pss.txt''
  exec master.dbo.xp_cmdshell @c, no_output
  end
 while (select top 1 count(*) from _s where d1 = 0) > 0
  begin
  select top 1 @id = id1, @ud = ud from _s where d1 = 0
  exec master.dbo.xp_trace_generate_event @event_class = 82, @event_text_data = ''blocking''
     , @binary_data = @ud
  update _s set d1 = 1 where id1 = @id
  end
end
finish:', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'performance', @command = N'-- performance check
declare @UserData nvarchar(128), @id1 int, @p varchar(3)
select @p = cast(rtrim(ltrim(substring(c1,charindex(''='',c1,1)+1,len(c1)))) as nvarchar (3))
  from _t1 where left(c1,3) =  ''@pe''
if upper(@p) != ''YES'' goto finish
truncate table _t1
insert into _t1 
select
  left([object_name],40)
+ left([counter_name],40)
+ left([instance_name],35)
+ cast([cntr_value] as nchar(11))
  from master.dbo.sysperfinfo where cntr_value > 0
while (select top 1 count(*) from _t1) > 0
 begin
 select top 1 @UserData = c1 from _t1
 EXEC master.dbo.xp_trace_generate_event @event_class = 83, @event_text_data = @UserData
 delete _t1 where left(c1,40) + substring(c1,41,40) + substring(c1,82,35) 
                = left(@UserData,40) + substring(@UserData,41,40) + substring(@UserData,82,35)
 end
finish:
if exists (select * from dbo.sysobjects where id = object_id(N''[_t1]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [_t1]
if exists (select * from dbo.sysobjects where id = object_id(N''[_t2]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [_t2]
if exists (select * from dbo.sysobjects where id = object_id(N''[_s]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [_s]', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule00seconds', @enabled = 1, @freq_type = 4, @active_start_date = 20001220, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule30seconds', @enabled = 0, @freq_type = 4, @active_start_date = 20001227, @active_start_time = 30, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule15seconds', @enabled = 0, @freq_type = 4, @active_start_date = 20001227, @active_start_time = 15, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule45seconds', @enabled = 0, @freq_type = 4, @active_start_date = 20001227, @active_start_time = 45, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:
Modification Type:MajorLast Reviewed:12/13/2002
Keywords:kbinfo KB286191 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.