Error Running generated job creation script

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Terry (anonymous_at_discussions.microsoft.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 14:30:29 -0800

I generated a script by right clicking on the job tree in
Enterprise Manager. I then tried to run the script to
create the job on another server and got the following
error. Please advise.

Server: Msg 515, Level 16, State 2, Procedure sp_add_job,
Line 105
Cannot insert the value NULL into column 'owner_sid',
table 'msdb.dbo.sysjobs'; column does not allow nulls.
INSERT fails.
The statement has been terminated.

HERE IS THE generated code:

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)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM msdb.dbo.sysjobs
  WHERE (name = N'ACT_ExportToDMS')
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Check if the job is a multi-server job
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysjobservers
              WHERE (job_id = @JobID) AND (server_id <>
0)))
  BEGIN
    -- There is, so abort the script
    RAISERROR (N'Unable to import job ''ACT_ExportToDMS''
since there is already a multi-server job with this
name.', 16, 1)
    GOTO QuitWithRollback
  END
  ELSE
    -- Delete the [local] job
    EXECUTE msdb.dbo.sp_delete_job @job_name =
N'ACT_ExportToDMS'
    SELECT @JobID = NULL
  END

BEGIN

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id =
@JobID OUTPUT , @job_name = N'dbo.ACT_ExportToDMS',
@owner_login_name = N'METALSRD1\envision', @description =
N'No description available.', @category_name =
N'[Uncategorized (Local)]', @enabled = 0,
@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'Step1', @command =
N'exec ACT_ExportToDMS', @database_name = N'ACTLocal',
@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'Sched1', @enabled = 1,
@freq_type = 4, @active_start_date = 20040308,
@active_start_time = 215, @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:

GO
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)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM msdb.dbo.sysjobs
  WHERE (name = N'ACT_PurgeOldData')
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Check if the job is a multi-server job
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysjobservers
              WHERE (job_id = @JobID) AND (server_id <>
0)))
  BEGIN
    -- There is, so abort the script
    RAISERROR (N'Unable to import
job ''ACT_PurgeOldData'' since there is already a multi-
server job with this name.', 16, 1)
    GOTO QuitWithRollback
  END
  ELSE
    -- Delete the [local] job
    EXECUTE msdb.dbo.sp_delete_job @job_name =
N'ACT_PurgeOldData'
    SELECT @JobID = NULL
  END

BEGIN

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id =
@JobID OUTPUT , @job_name = N'ACT_PurgeOldData',
@owner_login_name = N'METALSRD1\envision', @description =
N'No description available.', @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'Step 1', @command =
N'exec ACT_PurgeOldData', @database_name = N'ACTLocal',
@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'Sched1', @enabled = 1,
@freq_type = 4, @active_start_date = 20040308,
@active_start_time = 1000, @freq_interval = 1,
@freq_subday_type = 1, @freq_subday_interval = 0,
@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:

GO

Relevant Pages

  • Re: SQL state (error) is 37000
    ... idea what the purpose of changing the jobs is all about so I ... >This script is inherited and I am not sure why it was setup that way. ... >>> GOTO QuitWithRollback ... >>>COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL state (error) is 37000
    ... This script is inherited and I am not sure why it was setup that way. ... >> GOTO QuitWithRollback ... >>COMMIT TRANSACTION ... >>> The SQL Server Agent jobs run in a job cache. ...
    (microsoft.public.sqlserver.connect)
  • Changing schedules for a Job outside SQL Server
    ... Below is the script of a Job called "eFIMS_SendEmail" that I wish to ... For example, the SundayRun schedule will ... How can I use these values to change the schedules for the job? ... DECLARE @JobID BINARY ...
    (comp.databases.ms-sqlserver)
  • Re: Transactional Replication Problem
    ... > if (@JobID is NULL) ... > goto EndSave ... > QuitWithRollback: ...
    (microsoft.public.sqlserver.replication)
  • Re: Capturing the pid of a running program
    ... > the script and then have another script run some time later which reads ... > myprogram & ... > job control commands cannot be used non-interactively. ... echo $jobid>.pidlog ...
    (comp.unix.shell)