SQL Server add job agent T-SQL script issue

Question

I am using SQL Server 2008 Enterprise. I have created a very simple test SQL Server Agent job which just print out a string. And then I add a schedule to let the job run once a day.

Then I select from SQL Server Management Studio => Jobs => Select the test job => Script Job as => Drop and Create To, here is the generated scripts. My confusion is, when (at the bottom of the script) calls msdb.dbo.sp_add_jobschedule, there is an input parameter provided as an input parameter for this store procedure called @schedule_uid, but from MSDN, this parameter is an output parameter, and not an input parameter. Any ideas why is wrong and why such mismatching?

Here is MSDN page and the script generated from SQL Server Management Studio,

http://msdn.microsoft.com/en-us/library/ms366342.aspx

USE [msdb]
GO

/****** Object:  Job [TestJob]    Script Date: 08/05/2009 14:21:59 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'TestJob')
EXEC msdb.dbo.sp_delete_job @job_id=N'dd219677-7584-43de-b14c-b583b6ff619e', @delete_unused_schedule=1
GO

USE [msdb]
GO

/****** Object:  Job [TestJob]    Script Date: 08/05/2009 14:21:59 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/05/2009 14:21:59 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob', 
        @enabled=1, 
        @notify_level_eventlog=3, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [TestStep1]    Script Date: 08/05/2009 14:21:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'PRINT "Here is a test job"', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'TestJobSchedule', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20090805, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'68d6209d-86a7-48a4-86ea-51322f279a2d'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Tomalak · Accepted Answer · 2009-08-05 07:13:37Z

4

My guess would be that the @schedule_uid can either be defined and returned by SQL Server upon job creation (the OUTPUT case), or it can be set manually (obviously undocumented).

My guess would further be that the undocumented INPUT case is a means to maintaining the original uid during drop and recreate.

answered Aug 5, 2009 at 7:13

Tomalak

340k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

George2 Over a year ago

Thanks Tomalak, I am confused OUTPUT could indicate both input parameter and output parameter? Is it ambiguous?

Tomalak Over a year ago

Yes: OUTPUT only indicates that the parameter can be output by the procedure, it does not indicate that it cannot be input into the procedure. All parameters are INPUT by default, but OUTPUT parameters can communicate back additionally.

George2 Over a year ago

Sorry @Tomalak, I am totally lost. :-) OUTPUT parameter means our input values, output values from store procedure or both?

Tomalak Over a year ago

Both. :) Think of them as pass-by-reference parameters (as opposed to the default pass-by-value). Like all parameters, they go in, and unlike the normal parameters they can go out as well. That is, their value can be changed from inside the procedure.

George2 Over a year ago

Thanks, Tomalak. I have marked your reply as answered. I have a related question here, appreciate if you could help, stackoverflow.com/questions/1248244/…

Lachlan Wetherall · Accepted Answer · 2010-09-20 01:27:06Z

0

This problem with @schedule_uid can occur when using SSMS 2008 to script a job out from a SQL Server 2005 database, then trying to run the script against another SQL Server 2005 installation.

By using SSMS 2005 (not 2008) to generate the script and execute it, the problem does not occur.

answered Sep 20, 2010 at 1:27

Lachlan Wetherall

7857 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server add job agent T-SQL script issue

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related