|
|
You're in the
sql server replication
group:Monitoring & Starting Distribution Agent thru program
sql server replication:
there any way to start it programmatically.
Kumaresh,
how are you setting off the distribution agent? If it is through programming the activeX control, you could then have a look at the replication errors collection. This example applies to merge, but AFAIK it's much the same for transactional. http://www.windowsitpro.com/Files/09/39079/Listing_01.txt Alternatively you could look at MSdistribution_history and MSrepl_errors tables (or the job's history directly) HTH, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Kumaresh,
to let the client know, you could use the alert: 'Replication: agent failure' and have an email sent from there. Actually, what I do is send notification directly from the replication job belonging to the distribution agent with a specific message - in my case I only have SMTP, so I use an additional step in the distribution agent's job which is reached only on failure of the main step but if you have MAPI integrated, then you can just add the On Failure notification like in any other job. HTH, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Kumaresh,
this will give you any replication errors in the last 24 hours for a specific replication job. It hope it's OK for your purposes. SELECT DISTINCT MSdistribution_agents.name, MSdistribution_history.runstatus, MSdistribution_history.error_id, MSdistribution_history.start_time, MSdistribution_history.comments FROM MSdistribution_agents INNER JOIN MSdistribution_history ON MSdistribution_agents.id = MSdistribution_history.agent_id INNER JOIN MSrepl_errors ON MSdistribution_history.error_id = MSrepl_errors.id WHERE (MSdistribution_agents.name = N'PC-TEMPLATE-Northwind-PC-TEMPLATE-2') AND (MSdistribution_history.start_time BETWEEN DATEADD(dd, - 1, GETDATE()) AND GETDATE()) Rgds, Paul Ibison
Thanks for your valuable comments.
I have set the replication through the SQL Server Enterprise Manager. But the client complains that they saw the replication failure only after one day. And then after they had to set the replication again. Hence i thought of running a schedular program which checks for replication and start it if it had a failure. I also will be very happy if u could suggest or post some guide lines to proceed thanks once again [quoted text, click to view] "Paul Ibison" wrote:
> Kumaresh, > > how are you setting off the distribution agent? If it is through programming > the activeX control, you could then have a look at the replication errors > collection. This example applies to merge, but AFAIK it's much the same for > transactional. > > http://www.windowsitpro.com/Files/09/39079/Listing_01.txt > > Alternatively you could look at MSdistribution_history and MSrepl_errors > tables (or the job's history directly) > > HTH, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > >
Thanks Paul, That was very near to the solution expected.
But i also wanted to know is there any way by which i can handle it by myself, leaving the client undisturbed. Say for example running a scheduled program or some thing similar to that. so that i start the relpication without the intervention of the client Any suggestions would be great [quoted text, click to view] "Paul Ibison" wrote:
> Kumaresh, > to let the client know, you could use the alert: 'Replication: agent > failure' and have an email sent from there. > Actually, what I do is send notification directly from the replication job > belonging to the distribution agent with a specific message - in my case I > only have SMTP, so I use an additional step in the distribution agent's job > which is reached only on failure of the main step but if you have MAPI > integrated, then you can just add the On Failure notification like in any > other job. HTH, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > > > >
Thanks Plan
Here is the way am proceeding. First am gonna write a trigger on MSdistribution_history table to check for errors If any error occurs, then a COM object is called to delete MSREPL7 table created in oracle database Is this a reliable solution to proceed with. Also how can a distribution agent be restarted within a trigger Any help would be great [quoted text, click to view] "Paul Ibison" wrote:
> Kumaresh, > > this will give you any replication errors in the last 24 hours for a > specific replication job. It hope it's OK for your purposes. > > SELECT DISTINCT > MSdistribution_agents.name, > MSdistribution_history.runstatus, MSdistribution_history.error_id, > MSdistribution_history.start_time, > MSdistribution_history.comments > FROM MSdistribution_agents INNER JOIN > MSdistribution_history ON MSdistribution_agents.id = > MSdistribution_history.agent_id INNER JOIN > MSrepl_errors ON MSdistribution_history.error_id = > MSrepl_errors.id > WHERE (MSdistribution_agents.name = > N'PC-TEMPLATE-Northwind-PC-TEMPLATE-2') AND > (MSdistribution_history.start_time BETWEEN DATEADD(dd, - 1, GETDATE()) AND > GETDATE()) > > Rgds, > > Paul Ibison > >
Sorry I mean to type "Thanks Paul" but mistyped
[quoted text, click to view] > Thanks Plan
> Here is the way am proceeding. First am gonna write a trigger on > MSdistribution_history table to check for errors > If any error occurs, then a COM object is called to delete MSREPL7 table > created in oracle database > Is this a reliable solution to proceed with. > > Also how can a distribution agent be restarted within a trigger > > Any help would be great > > > "Paul Ibison" wrote: > > > Kumaresh, > > > > this will give you any replication errors in the last 24 hours for a > > specific replication job. It hope it's OK for your purposes. > > > > SELECT DISTINCT > > MSdistribution_agents.name, > > MSdistribution_history.runstatus, MSdistribution_history.error_id, > > MSdistribution_history.start_time, > > MSdistribution_history.comments > > FROM MSdistribution_agents INNER JOIN > > MSdistribution_history ON MSdistribution_agents.id = > > MSdistribution_history.agent_id INNER JOIN > > MSrepl_errors ON MSdistribution_history.error_id = > > MSrepl_errors.id > > WHERE (MSdistribution_agents.name = > > N'PC-TEMPLATE-Northwind-PC-TEMPLATE-2') AND > > (MSdistribution_history.start_time BETWEEN DATEADD(dd, - 1, GETDATE()) AND > > GETDATE()) > > > > Rgds, > > > > Paul Ibison > > > >
Kumaresh,
you won't be able to make a trigger on this system table, but you could schedule a job to run once a minute to poll the table. Rgds, Paul Ibison
Thanks paul,
I also want to know if i could start the replication from T-Sql Any reference articles would be very helpful [quoted text, click to view] "Paul Ibison" wrote:
> Kumaresh, > you won't be able to make a trigger on this system table, but you could > schedule a job to run once a minute to poll the table. > Rgds, > > Paul Ibison > > > >
Kumaresh,
to start synchronization fron TSQL, just use sp_start_job on the distribution (transactional) or merge (merge) agent's job. HTH, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Kumaresh,
to be honest we have covered various different issues on this thread and I've lost the plot a bit here :) Please can you detail exactly the setup you have created and why, and then me, Hilary or someone else can advise if it sounds reasonable. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
thanks a lot Paul.
I just want one confirmation with what i have done. I donn want any hard words from clients and thats the reason for disturbing you so often I have scheduled a job for every 5 minutes which calls a vbscript as you suggested My doubt is i connect to three databases to get the job done 1. Oracle local db to delete msrepl7 table 2. distribution database to check if error is generated and get the agent's name 3. msdb to start the distribution agent Is this a correct way. More over even if this solves my problem can you suggest some references where I can learn more about sql replication Thanks for all your patience [quoted text, click to view] "Paul Ibison" wrote:
> Kumaresh, > to start synchronization fron TSQL, just use sp_start_job on the > distribution (transactional) or merge (merge) agent's job. > HTH, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > >
Fine paul.
This was the problem:- I have set a transactional replication. It is working fine but for some errors (may be network or any other), it gets stopped. Client complains that there is no notification & hence they were able to start only after a day. For this problem, am running a schedular program to solve this My doubt is i connect to three databases to get the job done 1. Oracle local db to delete msrepl7 table 2. Distribution database is accessed to check if error is generated and get the agent's name 3. msdb to start the distribution agent all 3 are accessed in same program Is this a correct way. [quoted text, click to view] "Paul Ibison" wrote:
> Kumaresh, > to be honest we have covered various different issues on this thread and > I've lost the plot a bit here :) Please can you detail exactly the setup you > have created and why, and then me, Hilary or someone else can advise if it > sounds reasonable. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > >
OK this all sounds fine, given that you have said you don't want to edit the
existing jobs or alerts. I hadn't appreciated that MSDistribution_history is a user table. No doubt this is an oversight as it really should be a system one and if you're considering upgrading you might want to check in SQL Server 2005 to see if it is there. Anyway, good luck with it all. Paul Ibison "Kumaresh Sierra" <KumareshSierra@discussions.microsoft.com> wrote in message news:BE521CB1-677B-4060-B2F6-5E75B20FAEBE@microsoft.com... [quoted text, click to view] > Finally Paul, I have one more doubt > > MSDistribution_history table is found to be a user table. > > So am thinking of writing trigger on the table which would call a com > object > > I prefer to do this b'cos the frequency of the program being run can be > minized > > thanks in advance > > "Kumaresh Sierra" wrote: > >> Fine paul. >> >> This was the problem:- >> I have set a transactional replication. It is working fine but for some >> errors (may be network or any other), it gets stopped. Client complains >> that >> there is no notification & hence they were able to start only after a >> day. >> >> For this problem, am running a schedular program to solve this >> >> My doubt is i connect to three databases to get the job done >> 1. Oracle local db to delete msrepl7 table >> 2. Distribution database is accessed to check if error is generated and >> get >> the agent's name >> 3. msdb to start the distribution agent >> >> all 3 are accessed in same program >> >> Is this a correct way. >> >> >> "Paul Ibison" wrote: >> >> > Kumaresh, >> > to be honest we have covered various different issues on this thread >> > and >> > I've lost the plot a bit here :) Please can you detail exactly the >> > setup you >> > have created and why, and then me, Hilary or someone else can advise if >> > it >> > sounds reasonable. >> > Cheers, >> > Paul Ibison SQL Server MVP, www.replicationanswers.com >> > (recommended sql server 2000 replication book: >> > http://www.nwsu.com/0974973602p.html) >> > >> > >> >
Finally Paul, I have one more doubt
MSDistribution_history table is found to be a user table. So am thinking of writing trigger on the table which would call a com object I prefer to do this b'cos the frequency of the program being run can be minized thanks in advance [quoted text, click to view] "Kumaresh Sierra" wrote:
> Fine paul. > > This was the problem:- > I have set a transactional replication. It is working fine but for some > errors (may be network or any other), it gets stopped. Client complains that > there is no notification & hence they were able to start only after a day. > > For this problem, am running a schedular program to solve this > > My doubt is i connect to three databases to get the job done > 1. Oracle local db to delete msrepl7 table > 2. Distribution database is accessed to check if error is generated and get > the agent's name > 3. msdb to start the distribution agent > > all 3 are accessed in same program > > Is this a correct way. > > > "Paul Ibison" wrote: > > > Kumaresh, > > to be honest we have covered various different issues on this thread and > > I've lost the plot a bit here :) Please can you detail exactly the setup you > > have created and why, and then me, Hilary or someone else can advise if it > > sounds reasonable. > > Cheers, > > Paul Ibison SQL Server MVP, www.replicationanswers.com > > (recommended sql server 2000 replication book: > > http://www.nwsu.com/0974973602p.html) > > > >
Paul,
Am just bugged up. When i run sp_help MSdistribution_history it shows as "User Table" in Object type column. But in enterprise manager, it shows as system table. I was able to write a simple trigger to insert into a temporary table and it did work fine. When i tried implementing the relevant steps within the trigger and start the agent, then after am not able to access the database thru enterprise manager and also am not able to query MSDistribution_History table Any suggestion would be great [quoted text, click to view] "Paul Ibison" wrote:
> OK this all sounds fine, given that you have said you don't want to edit the > existing jobs or alerts. I hadn't appreciated that MSDistribution_history is > a user table. No doubt this is an oversight as it really should be a system > one and if you're considering upgrading you might want to check in SQL > Server 2005 to see if it is there. Anyway, good luck with it all. > Paul Ibison > > > > "Kumaresh Sierra" <KumareshSierra@discussions.microsoft.com> wrote in > message news:BE521CB1-677B-4060-B2F6-5E75B20FAEBE@microsoft.com... > > Finally Paul, I have one more doubt > > > > MSDistribution_history table is found to be a user table. > > > > So am thinking of writing trigger on the table which would call a com > > object > > > > I prefer to do this b'cos the frequency of the program being run can be > > minized > > > > thanks in advance > > > > "Kumaresh Sierra" wrote: > > > >> Fine paul. > >> > >> This was the problem:- > >> I have set a transactional replication. It is working fine but for some > >> errors (may be network or any other), it gets stopped. Client complains > >> that > >> there is no notification & hence they were able to start only after a > >> day. > >> > >> For this problem, am running a schedular program to solve this > >> > >> My doubt is i connect to three databases to get the job done > >> 1. Oracle local db to delete msrepl7 table > >> 2. Distribution database is accessed to check if error is generated and > >> get > >> the agent's name > >> 3. msdb to start the distribution agent > >> > >> all 3 are accessed in same program > >> > >> Is this a correct way. > >> > >> > >> "Paul Ibison" wrote: > >> > >> > Kumaresh, > >> > to be honest we have covered various different issues on this thread > >> > and > >> > I've lost the plot a bit here :) Please can you detail exactly the > >> > setup you > >> > have created and why, and then me, Hilary or someone else can advise if > >> > it > >> > sounds reasonable. > >> > Cheers, > >> > Paul Ibison SQL Server MVP, www.replicationanswers.com > >> > (recommended sql server 2000 replication book: > >> > http://www.nwsu.com/0974973602p.html) > >> > > >> > > >> > > >
In that case I'd resort to polling the table at one minute intervals using a
scheduled job. Paul Ibison
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |