add job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

add job

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2007-05-16 : 04:28:02

Why I get this error:"
Server: Msg 16917, Level 16, State 2, Procedure sp_delete_job_references, Line 41
Cursor is not open.
"

DECLARE @NotifySource VARCHAR(128), @Problem INT, @ERROR INT, @ErrMessage VARCHAR(500)
DECLARE @ServerName VARCHAR(30)
SET @NotifySource = 'Shadow: Proc ' + OBJECT_NAME( @@PROCID )
SET @Problem = 0

DECLARE @JobID BINARY(16), @ReturnCode INT, @jobName NVARCHAR(100)
SET @ReturnCode = 0
SET @jobName = N'Migrate_CifsEvent_' + 'ShadowServerName'

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Migration') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Migration'

SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE [name] = @jobName

IF EXISTS ( SELECT *
FROM msdb..sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)
)
BEGIN
RAISERROR (N'Unable to import job ''Migrate_CifsEvent_[ShadowDB]'' since there is already a multi-server job with this name.', 16, 1)
SET @Problem = 1
RETURN
END

IF @JobID IS NOT NULL
BEGIN
EXECUTE msdb.dbo.sp_delete_job @job_name = @jobName
SET @JobID = NULL
END

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT,
@job_name = @jobName,
@owner_login_name = N'sa', @description = N'No description available.',
@category_name = N'Migration', @enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 0
SET @ERROR = @@ERROR
IF @ERROR <> 0 OR @ReturnCode <> 0
BEGIN
EXEC spNotification @Source = @NotifySource, @Action = 1000, @Param = 0, @message = 'Error: msdb.dbo.sp_add_job FAIL'
SET @Problem = 1
END

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CopyTable',
@command = N'EXEC Migration_StageII_CifEventStep1',
@database_name = N'vrnsShadowDB', -- DON'T PUT [...]
@server = NULL,
@database_user_name = N'sa', @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
SET @ERROR = @@ERROR
IF @ERROR <> 0 OR @ReturnCode <> 0
BEGIN
EXEC spNotification @Source = @NotifySource, @Action = 1000, @Param = 0, @message = 'Error: msdb.dbo.sp_add_jobstep 1 FAIL'
SET @Problem = 1
END

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Calculate Statistics',
@command = N'EXEC spCalcEventStats ',
@database_name = N'vrnsShadowDB', -- DON'T PUT [ ]
@server = NULL,
@database_user_name = N'sa', @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
SET @ERROR = @@ERROR
IF @ERROR <> 0 OR @ReturnCode <> 0
BEGIN
EXEC spNotification @Source = @NotifySource, @Action = 1000, @Param = 0, @message = 'Error: msdb.dbo.sp_add_jobstep 2 FAIL'
SET @Problem = 1
END

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
SET @ERROR = @@ERROR
IF @ERROR <> 0 OR @ReturnCode <> 0
BEGIN
EXEC spNotification @Source = @NotifySource, @Action = 1000, @Param = 0, @message = 'Error: msdb.dbo.sp_update_job FAIL'
SET @Problem = 1
END

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Between2_7',
@enabled = 1, @freq_type = 4,
@active_start_date = 20070502, @active_start_time = 20000,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 70000

SET @ERROR = @@ERROR
IF @ERROR <> 0 OR @ReturnCode <> 0
BEGIN
EXEC spNotification @Source = @NotifySource, @Action = 1000, @Param = 0, @message = 'Error: msdb.dbo.sp_add_jobschedule FAIL'
SET @Problem = 1
END

SET @ServerName = CAST( SERVERPROPERTY( 'ServerName') AS VARCHAR(30))
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver /* @job_id = @JobID, */ @job_name = @jobName , @server_name = @ServerName
SET @ERROR = @@ERROR
IF @ERROR <> 0 OR @ReturnCode <> 0
BEGIN
EXEC spNotification @Source = @NotifySource, @Action = 1000, @Param = 0, @message = 'Error: msdb.dbo.sp_add_jobserver FAIL'
SET @Problem = 1
END

IF @@TRANCOUNT > 0 COMMIT

Noam Graizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources