9.3.1.2 Script B - Administration and Management Portal Guide

Script B

USE [msdb]

/****** Object: Job [purge sequence tables] Script Date: 02/18/2010 10:24:21 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

DECLARE @YourDatabaseName sysname

DECLARE @YourServerName sysname

DECLARE @stmt nvarchar(max);

-- set your server and database names here !!!!!!!!

SELECT @YourDatabaseName = '',@YourServername = ''

--important step !!!!!!!!!

SELECT @stmt = N'DECLARE @RC int

DECLARE @sequenceName varchar(40)

-- TODO: Set parameter values here.

SELECT @sequenceName = ''dbo.X_PaymentReceiptSequence''

EXECUTE @RC = [' + @YourDatabaseName + '].[dbo].[XP_SequencePurgeVal]

@sequenceName

SELECT @sequenceName = ''dbo.X_PaymentTechSequence''

EXECUTE @RC = [' + @YourDatabaseName + '].[dbo].[XP_SequencePurgeVal]

@sequenceName';

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/18/2010 10:24:21 ******/

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'purge sequence tables',

@enabled=1,

@notify_level_eventlog=0,

@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'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [purge sequence tables] Script Date: 02/18/2010 10:24:21 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'purge sequence tables',

@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= @stmt,

@database_name=@YourDatabaseName,

@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'PurgeSequenceTables',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20100107,

@active_end_date=99991231,

@active_start_time=30000,

@active_end_time=235959,

@schedule_uid=N'3bc8f076-d9fc-4c39-988d-391c929f0183'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = @YourServerName

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Related Topics

The DBA steps (somewhat manual)

iNovah Administration and Management Portal Guide Copyright © 2010 by System Innovators, a division of N. Harris Computers, Inc. The System Innovators logo and name is a registered trademark of System Innovators. iNovah and the iNovah logo are both trademarks of System Innovators. Windows is a registered trademark of Microsoft Corporation. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose other than the customer’s own use without written permission from System Innovators. System Innovators 10550 Deerwood Park Blvd., Suite 700 Jacksonville, Florida 32256