January 11, 2010 at 1:33 pm
Hi All,
I have replication sql server 2k5 to 2k5. Everytime we refresh staging database, we have a master job that does:
1. drop replication (publishers and subscribers).
2. Restores the databases
3. Recreates replication.
(I have scripted out the drop and create scripts using the generate scripts option GUI)
Now step 3 that recreates replication, is creating the job names as default with default schedules.
Is there a way that I can create Job names that I can edit in the scripts, so that when I recreate replication, it does not create default job names instead it created the jobs with name that I have put in the scripts.
I am not sure what option to change in script for job names and schedule.
Any ideas !
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 18, 2010 at 1:24 pm
Still working ... any ideas Experts ? :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 18, 2010 at 1:56 pm
As I understand your question, you want to setup the distribution job name and schedule, which can be setup in
sp_addsubscription
please see BOL for options.
I think option
@distribution_job_name & @frequency... is what you are looking for.
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 18, 2010 at 3:19 pm
Vinay Thakur-585143 (1/18/2010)
As I understand your question, you want to setup the distribution job name and schedule, which can be setup insp_addsubscription
please see BOL for options.
I think option
@distribution_job_name & @frequency... is what you are looking for.
Vinay,
Thanks for your help. Actually, I have gone through "sp_addsubscription " and tried the options you mentioned, but had no luck. IF I mention the @distribution_job_name = 'Foo_Job' or some other name then also it creates the default job name and after that I have to manually rename the job.
I want to rename in the REPL-Distribution category. For recreating the replication, I am using the scripts generated from the generateScripts option from the GUI.
Its a pain to have manual steps when all your process is automated ! :hehe:
Let me know if you have any other workaround for this !
Thanks again,
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 19, 2010 at 3:13 am
I do not think there is any way to get your jobs generated with meaningful names. The only option is to change the names after they have been generated.
I have not looked at this particular problem, but have done something similar for MDW and PBM jobs.
An Agent job has both a JobId and a Name. The JobId is often used in metadata tables to identify the job, and the Name is shown in SSMS and is what people normally use to identify a job. Schedules also have both a ScheduleId and a Name, and the same convention applies.
It should be possible to query the metadata about the replication subscriptions and find the JobIds. You could then update the job Name for the given JobId with a suitable description generated from the metadata. You might also want to change the schedule names so they are also meaningful. Next, wrap all of this into a stored proc, and run this proc after you have rebuilt your subscriptions.
Finally, publish this proc so we can all benefit...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 19, 2010 at 8:30 am
EdVassie (1/19/2010)
I do not think there is any way to get your jobs generated with meaningful names. The only option is to change the names after they have been generated.I have not looked at this particular problem, but have done something similar for MDW and PBM jobs.
An Agent job has both a JobId and a Name. The JobId is often used in metadata tables to identify the job, and the Name is shown in SSMS and is what people normally use to identify a job. Schedules also have both a ScheduleId and a Name, and the same convention applies.
It should be possible to query the metadata about the replication subscriptions and find the JobIds. You could then update the job Name for the given JobId with a suitable description generated from the metadata. You might also want to change the schedule names so they are also meaningful. Next, wrap all of this into a stored proc, and run this proc after you have rebuilt your subscriptions.
Finally, publish this proc so we can all benefit...
Thanks EdVassie !
The problem is that when replication is recreated everytime, it is created with a new name e.g adventureworks 9 or [servername]adventureworks adventureworksDW pubs 1-1, so this means that based on the new name, I am not able to figure it out how to automate this to change the job name.
I have tried various means to achieve this but it is not working !
Any ideas ...
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 19, 2010 at 9:19 am
we never required to change the distribution job, and yes @distribution_job_name is for internal use only :(.
For now I could see the workaround like this.
declare @JobID varchar(1000)
select @JobID=job_id from sysjobs where category_id =(
select category_id from syscategories where name='REPL-Distribution') --generally 10
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'abc'
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 19, 2010 at 9:26 am
Vinay Thakur-585143 (1/19/2010)
we never required to change the distribution job, and yes @distribution_job_name is for internal use only :(.For now I could see the workaround like this.
declare @JobID varchar(1000)
select @JobID=job_id from sysjobs where category_id =(
select category_id from syscategories where name='REPL-Distribution') --generally 10
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'abc'
Thanks Vinay,
I will give it a try and let you know. Will it take into account for just one job or for all jobs which are categorized in REPL-Distribution .. category? If that is the case then, this should be used with caution !
I will try on my dev environment and will post it !
Thanks for all your help 🙂
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 19, 2010 at 10:44 am
More information on this.
exec sp_addsubscription ....,@distribution_job_name ='Distribution1'
Here @distribution_job_name is system use only but if we provide value to this, it will keep it for internal tables for replication,and which we can use it for renaming job name.
declare @JobID uniqueidentifier
select @JobID=job_id from distribution.dbo.MSdistribution_agents a
where name='Distribution1'
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'NewJobName'
Just for your information.
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 19, 2010 at 11:15 am
Vinay Thakur-585143 (1/19/2010)
More information on this.exec sp_addsubscription ....,@distribution_job_name ='Distribution1'
Here @distribution_job_name is system use only but if we provide value to this, it will keep it for internal tables for replication,and which we can use it for renaming job name.
declare @JobID uniqueidentifier
select @JobID=job_id from distribution.dbo.MSdistribution_agents a
where name='Distribution1'
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'NewJobName'
Just for your information.
Thanks Vinay,
I am using below script for recreating rep (this is just one part of the whole script)
use [DB_name]
exec sp_addsubscription @publication = N'[Pub_server_name]', @subscriber = N'[Sub_server_name]', @destination_db = N'[DB_dest_name]', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'Program', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'[Pub_server_name]', @subscriber = N'[Sub_server_name]', @subscriber_db = N'[DBr_name]', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
I see no where @distribution_job_name ... as it might be the case as the script is generated from GUI.
So how can I know what job name is generated everytime I recreated replication. Correct me if I am understanding wrong !
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 19, 2010 at 11:56 am
use [DB_name]
exec sp_addsubscription @publication = N'[Pub_server_name]', @subscriber = N'[Sub_server_name]', @destination_db = N'[DB_dest_name]', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'Program', @update_mode = N'read only', @subscriber_type = 0,
@distribution_job_name ='[Distribution1]'
exec sp_addpushsubscription_agent @publication = N'[Pub_server_name]', @subscriber = N'[Sub_server_name]', @subscriber_db = N'[DBr_name]', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
------------------get the distribution job for Distribution1
declare @JobID uniqueidentifier
select @JobID=job_id from distribution.dbo.MSdistribution_agents a
where name='Distribution1'
---rename the job with newjobname
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'NewJobName'
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 19, 2010 at 1:04 pm
Vinay Thakur-585143 (1/19/2010)
use [DB_name]exec sp_addsubscription @publication = N'[Pub_server_name]', @subscriber = N'[Sub_server_name]', @destination_db = N'[DB_dest_name]', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'Program', @update_mode = N'read only', @subscriber_type = 0,
@distribution_job_name ='[Distribution1]'
exec sp_addpushsubscription_agent @publication = N'[Pub_server_name]', @subscriber = N'[Sub_server_name]', @subscriber_db = N'[DBr_name]', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
------------------get the distribution job for Distribution1
declare @JobID uniqueidentifier
select @JobID=job_id from distribution.dbo.MSdistribution_agents a
where name='Distribution1'
---rename the job with newjobname
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'NewJobName'
Vinay,
I really appreciate your help.
Still it is not working with @distribution_job_name ='[Distribution1]'
This is what I used:
use [AdventureWorks]
exec sp_addsubscription @publication = N'Adventureworks_pub', @subscriber = N'HOME-Server', @destination_db = N'AdventureWorksDW', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0, @distribution_job_name ='Distribution1'
Still it creates the default job as: Job 'HOME-Server-AdventureWorks-Adventureworks_pub-HOME-Server-23' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
I want that the job should be created with name "Distribution1"
Did you tried this.. Its a pain ;-).. as why it is not taking the @distribution_job_name parameter when it is specified.
Any ideas !
**Edit: --> I can rename the job using your method 🙂 But in actual, I have to rename 11 jobs that are created as distribution agent jobs that references replication from different servers across different databases. I am thinking of putting it in create replication script, but once I figure out how to generate the job name I want, I will try it out.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 19, 2010 at 1:14 pm
please follow below steps as well. as @distribution_jobname will not create job with new name, we have to refer that name to rename our job name with the below steps.
------------------get the distribution job for Distribution1
declare @JobID uniqueidentifier
select @JobID=job_id from distribution.dbo.MSdistribution_agents a
where name='Distribution1'
---rename the job with newjobname
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'NewJobName'
this will rename the job with "newjobname"
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 19, 2010 at 1:33 pm
@distribution_jobname will not create job with new name
I think that I was not clear of what I want to do .. Let me try explaining..
I want the distribution job name created with the name that I want .. a friendly name (for ease of maintenance)
What you are telling is that still a manual process ! ... Every time the replication is recreated I don't actually know with what name it will get created ... i.e here our "Distribution1" will be unknown .
e.g 1 time --> HOME-Server-AdventureWorks-Adventureworks_pub-HOME-Server-23
2nd time --> HOME-Server-AdventureWorks-Adventureworks_pub-HOME-Server-27
So I want to over come to find out this job and rename .. either through GUI or using you method !
Let me know if there is any other approach ...
Thanks for you help ..
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 19, 2010 at 2:08 pm
Sorry for confusion,
follow this
declare @JobID uniqueidentifier
select @JobID=job_id from distribution.dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db=['PublisherDB'] and
a.subscriber_db=['Destination_DB'] and
a.subscriber_id=s.srvid and
s.srvname=['SubsriberServer']
--this will give jobid for the replication we have created, just use that jobid and rename the job.
exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N'NewJobName'
I also blog about this. please read once.
http://rdbmsexperts.com/Blogs/?p=40
HTH
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply