Monday, November 29, 2010

@schedule_uid is not a parameter for procedure sp_add_jobschedule

I received this error when trying to copy a SQL job from one server to another, by trying to script out the create statement:

Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0

@schedule_uid is not a parameter for procedure sp_add_jobschedule

The problem is that the parameters for the stored procedure, sp_add_jobschedule, have changed from SQL2005 to SQL2008, so you need to change the script to match.

- SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (a uniqueidentifier)

- All you have to do is change the parameter name and data-type to the correct value
    If it's @schedule_uid, change it to @schedule_id.
    Then, find an un-used integer value in: select * from sysjobschedules (MSDB database)

- If you are going the other way, you can change the parameter to @schedule_uid, then create a new
   uniqueidentifier by running:  select newid()