Sometimes the following error occurs when you create a SQL Server job with an SQL statement:
- MSG 515, Level 16, State 2, procedure sp_add_job, line 137th
- Cannot insert value NULL into column ' owner_sid ', table ' msdb.dbo.sysjobs '; column does not allow null values. INSERT failed.
- Statement has been terminated.
This may be related to the database login ID created for the job, which needs to be the owner of the database (my is SA), so
- @owner_login_name=n' Hyseritc003/wellcomm ',
- The n ' Hyseritc003/wellcomm ' in is changed to the owner of thedatabase, such as SA.
- Use [msdb]
- GO
- /****** object: Job [24th Sox Reminders] Script date: 11/25/2008 09:40:05 ******/
- BEGIN TRANSACTION
- DECLARE @ReturnCode INT
- SELECT @ReturnCode = 0
- /****** object: Jobcategory [[[Uncategorized (Local]]]] Script Date: 11/25/2008 09:40:06 ******/
- 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' 24th Sox reminders ',
- @enabled = 1,
- @notify_level_eventlog = 0,
- @notify_level_email = 0,
- @notify_level_netsend = 0,
- @notify_level_page = 0,
- @delete_level = 0,
- @description =n' 24th Control point leader did not complete control point information, SMS reminders. ',
- @category_name =n' [Uncategorized (Local)] ',
- @owner_login_name =n 'hyseritc003/wellcomm ', - - will ' Hyseritc003/wellcomm ' Change to ' sa '.
- @job_id = @jobId OUTPUT
- IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
- /****** object: Step [24th Reminders] Script Date: 11/25/2008 09:40:07 ******/
- EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name =n' 24th reminders ',
- @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 '-------SOX 24th morning execution, SMS reminders Control Point--------
- DECLARE @Principal varchar (50)
- If exists (select distinct Principal from T_sox where auditingstatus<1 and Convert (varchar (7), writetime,120) =convert ( varchar (7), GETDATE (), 120)
- Declare cur_principal cursor for SELECT DISTINCT principal from T_sox where auditingstatus<1 and Convert (varchar (7) , writetime,120) =convert (varchar (7), GETDATE (), +)
- Open Cur_principal
- FETCH NEXT from Cur_principal to @Principal
- While (@ @FETCH_STATUS =0)
- Begin
- DECLARE @uid varchar (50)
- Select @uid =user_id from T_user Where [email protected]
- Insert into T_sms_interface (wf_id,wfi_id,form_id,serial_no,receive_user_id,sms_content,create_time)
- VALUES (' 0', '1',' Sox control points ', ' 1 ',@uid,' Today is number 24th, please fill in the Sox control point information in the business management system. Thank you. ", GETDATE ())
- FETCH NEXT from Cur_principal to @Principal
- End
- Close Cur_principal
- Deallocate Cur_principal
- ----------------------------------------------‘,
- @database_name =n' Hybizas ',
- @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 @[email protected], @name =n' 24th morning at 8 o ',
- @enabled = 1,
- @freq_type = 16,
- @freq_interval = 24,
- @freq_subday_type = 1,
- @freq_subday_interval = 0,
- @freq_relative_interval = 0,
- @freq_recurrence_factor = 1,
- @active_start_date = 20080728,
- @active_end_date = 99991231,
- @active_start_time = 80000,
- @active_end_time =235959
- 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:
