How to Change Replication Job Names in Scripts? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Import/Export (DTS) and Replication (2000)

How to Change Replication Job Names in Scripts?

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-07-31 : 17:58:30

I have been unsuccessful at changing job names in my replication scripts. I script replication including jobs, change the job names to follow our naming convention and then re-run the script. SQL Server creates my job names plus its own. For example, I see the following jobs for Distribution.

Database1:Database-1 (created by SQL Server)
REPL - Distribution - Database1 (The name I modified in the script)

If in EM I click on properties under Distribution Agent I find that the job created by SQL Server is the one being used, not mine. I am able to change the name of the job once it is created, but this is a pain.

What am I doing wrong?
Does everyone keep the default names created by SQL Server?

Thanks, Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-31 : 18:05:18

You have naming standards for jobs? We've never gone this far with standards and yes I usually keep the name of the jobs that SQL Server creates.

Can you post your script? Maybe, you are missing a modification. There are multiple jobs related to replication, so are you sure that you are modifying every single thing that you need to?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-31 : 18:22:01

The part that needs to be modified is the name parameter for sp_add_job.

Here's partial code (two parts need to be modified and are in red, one part for the check if the job exists and the other to create it, I didn't post the code where it creates the steps and job schedules, just the main part):


select @JobID = job_id from msdb.dbo.sysjobs where (name = N'SDDEVSQL1\QTW-SensorTRACS-SensorTRACS_Report')
if (@JobID is NULL)
BEGIN
  execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'SDDEVSQL1\QTW-SensorTRACS-SensorTRACS_Report', @enabled = 0, @start_step_id = 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'REPL-Snapshot', @owner_login_name = N'distributor_admin' 
  if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

Tara

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-07-31 : 18:24:45

That's what I did. It creates my job successfully, but for some reason SQL Server creates a second job using its own naming convention. This second job is the one it decides to associate with its replication agent.

Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-31 : 18:30:40

Just found another place that needs to be modified:


-- Adding the transactional publication
exec sp_addpublication @publication = N'GT_QTRACS - GT', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of GT_QTRACS database from Publisher SDDEVSQL1\GTW.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @logreader_job_name = N'SDDEVSQL1\GTW-GT_QTRACS-8'
exec sp_addpublication_snapshot @publication = N'GT_QTRACS - GT',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @snapshot_job_name = N'SDDEVSQL1\GTW-GT_QTRACS-GT_QTRACS - GT-22'
GO

Tara

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-08-01 : 08:33:56

Thanks, but unfortunately I've already tried changing that name. When you lookup the commands in BOL and try to see how Snapshot_Job_Name is used it only says "For Internal Use Only". The other strange thing I noticed last night was SQL Server keeps appending numbers at the end of its replication jobs. If I disable publishing and make sure all jobs are deleted and then re-run the replication script, SQL Server appends a new set of numbers to the end of the job names. That tells me it is storing job information (for active and deleted jobs) in some system table or registry entry. Strange.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources