Hello,
Right now, I have an alert set up in the SQL Server but is it possible for a
C#/C/C++/VB program to detect the replication agent failure? If so, how can
it be done?
Thanks in advance,
Frank
query the repl_errors table in the distirbution database.
select name, Publisher=p.srvname, publisher_db, publication,
Subscriber=s.srvname, subscriber_db, MSdistribution_history.time, comments,
error_type_id,source_type_id, source_name, error_code,error_text from
MSdistribution_history
join msrepl_errors on MSdistribution_history.error_id=msrepl_errors.id
join msdistribution_agents on
msdistribution_agents.id=MSdistribution_history.ag ent_id
join master.dbo.sysservers as p on p.srvid=publisher_id
join master.dbo.sysservers as s on s.srvid=publisher_id
order by 1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJY" <FJY@.discussions.microsoft.com> wrote in message
news:1F219C21-2032-422F-AC54-C0D5BBC15CAA@.microsoft.com...
> Hello,
> Right now, I have an alert set up in the SQL Server but is it possible for
> a
> C#/C/C++/VB program to detect the replication agent failure? If so, how
> can
> it be done?
> Thanks in advance,
> Frank
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Wednesday, March 21, 2012
How to detect replication doesnt work and get noticed?
I want to get notice by netsend, email or any other methods
when replication doesn't work or malfunction.
Does anyone know how to do this or any other solution you use for this kind of issue?
Thank you..Every agent that runs your replication (snapshot, logreader, distributot, and queuereader) have associated SQLAgent jobs. All you have to do is modify those jobs by going to Notification tab of the job properties and selecting notification method you'd like to get. You should have an appropriate operator set up before, or you can do it right there by selecting New Operator.|||Thank you for reply..
The main problem I have is how to find out if the replication doesn't work..
I can notice with agent history or icon.
Sometimes the icon become X marked, some times agent keep trying to connect and generate error with forever loop, sometimes gives I/O error while working..
How to find out these and what's best way to find out if replication doesn't work properly?|||Have you looked at what I posted about?|||Yes I did..
I knew that part..
Could you give me more detail with example with specific error cases?
Then, I understand more clearly.|||see attachment|||Thank you for the image..
I think I got it at this level..
I just wonder is there a way to run a custom application when job fails rather than sending Net-send..
Since I have to check hundreds of systems replicated, if I use net-send I will get too much of it and hard to maintain.
If I can run my custome application reporting problem organized, it will be easy to maintain.|||In that case you should create a batch file and call it from task window .
In SQL Scheduler when you click on the task and click advanced it says
" On failure action: and then there is a drop down . All you have to do is add another task to the existing job and call it here
It will say
"On failure Action : Goto Step #2" In that step #2 you can all the windows batch file .. (which can contain anything like ISQL, another program ...)|||Thank you!
I got it!|||Another way of doing it would be to create a custom alert and a scheduled task. When conditions for the alert are met you can invoke that scheduled task that may contain ... see previous post.
when replication doesn't work or malfunction.
Does anyone know how to do this or any other solution you use for this kind of issue?
Thank you..Every agent that runs your replication (snapshot, logreader, distributot, and queuereader) have associated SQLAgent jobs. All you have to do is modify those jobs by going to Notification tab of the job properties and selecting notification method you'd like to get. You should have an appropriate operator set up before, or you can do it right there by selecting New Operator.|||Thank you for reply..
The main problem I have is how to find out if the replication doesn't work..
I can notice with agent history or icon.
Sometimes the icon become X marked, some times agent keep trying to connect and generate error with forever loop, sometimes gives I/O error while working..
How to find out these and what's best way to find out if replication doesn't work properly?|||Have you looked at what I posted about?|||Yes I did..
I knew that part..
Could you give me more detail with example with specific error cases?
Then, I understand more clearly.|||see attachment|||Thank you for the image..
I think I got it at this level..
I just wonder is there a way to run a custom application when job fails rather than sending Net-send..
Since I have to check hundreds of systems replicated, if I use net-send I will get too much of it and hard to maintain.
If I can run my custome application reporting problem organized, it will be easy to maintain.|||In that case you should create a batch file and call it from task window .
In SQL Scheduler when you click on the task and click advanced it says
" On failure action: and then there is a drop down . All you have to do is add another task to the existing job and call it here
It will say
"On failure Action : Goto Step #2" In that step #2 you can all the windows batch file .. (which can contain anything like ISQL, another program ...)|||Thank you!
I got it!|||Another way of doing it would be to create a custom alert and a scheduled task. When conditions for the alert are met you can invoke that scheduled task that may contain ... see previous post.
Monday, March 19, 2012
How to detect at the subscriber that a replication is being (re)initialized.
Is there a method/query to discover that the subscriber's databasereplication is being reinitialized?
The problem is that the applications which is connects to this database crashes when the replication is (re)initializing.
Thanx, RalfYou would have to query the tables at the distribution database, this type of info is not stored at the subscriber. You could also try to reinitialize during offpeak hours.
How to detect at the subscriber that a replication is being (re)initialized.
Is there a method/query to discover that the subscriber's databasereplication is being reinitialized?
The problem is that the applications which is connects to this database crashes when the replication is (re)initializing.
Thanx, RalfYou would have to query the tables at the distribution database, this type of info is not stored at the subscriber. You could also try to reinitialize during offpeak hours.
How to detach replication dbs?
Detach/attach is breaking the link between replication and these databases.
Depending on some specifics, there may be several ways to get what you want to do.
First, knowing what version and edition will tell us what tools we have to work with.
Off the top of my head, you could tear down your replication setup, detach/attach, and recreate the replication config. Not pretty, but you could automate it with scripts.
You can use backup/restore instead of detach/attach.
You could possibly create database snapshots of all the databases, and then to reset use RESTORE DATABASE <dbname> FROM DATABASE_SNAPSHOT=<snapshot name> to limit the amount of data being moved.
|||The situation is: my project is partitioned in two db servers, which all use SQL Server 2005. db01 is on server01 and has replication in server02, db02 is on server02 and has replication in server01. Each time there is a new build of the project, I have to do performance test for it. Unluckily, prepare for the performance test data need 2 days, each time do deployment for the new build, the db will be re-deployed, so all data will lose. I have keep the old build database and data, the most important thing is how to resotore data into new build. I ever used "bcp" to export and import data. But I don't know when replication will end up. So I think maybe detach/attach can help.
How to detach a database with replication db?
There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.you cannot detach a replicated database. You could do backup/restore instead.
Friday, February 24, 2012
how to delete distributionDB
after i disable replication. the distributionDB is still there.
where i try to delete it . a error message occured as below:
Error 3724: cannot drop the database because it is being used for replication
please help
Cheers
nick
back up your master database. Issue the following query:
UPDATE master..sysdatabases SET category = 0 where name='distribution'
delete the distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:E112AE00-DABA-4FB1-BE70-0CAA0791D62F@.microsoft.com...
> after i disable replication. the distributionDB is still there.
> where i try to delete it . a error message occured as below:
> Error 3724: cannot drop the database because it is being used for
replication
> please help
> Cheers
> nick
>
|||when i tried to run
UPDATE master..sysdatabases SET category = 0 where name='nickDistribtuion'
i got error as below:
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
"Hilary Cotter" wrote:
> back up your master database. Issue the following query:
> UPDATE master..sysdatabases SET category = 0 where name='distribution'
> delete the distribution database.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <Nick@.discussions.microsoft.com> wrote in message
> news:E112AE00-DABA-4FB1-BE70-0CAA0791D62F@.microsoft.com...
> replication
>
>
|||sp_configure 'allow updates', 1
reconfigure with override
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:BB79FB0A-8BED-41D9-BE60-E539DCCFD9D7@.microsoft.com...
> when i tried to run
> UPDATE master..sysdatabases SET category = 0 where
name='nickDistribtuion'
> i got error as below:
> Ad hoc updates to system catalogs are not enabled. The system
administrator[vbcol=seagreen]
> must reconfigure SQL Server to allow this.
> "Hilary Cotter" wrote:
|||it works. thanks a lot.
Nick
"Hilary Cotter" wrote:
> sp_configure 'allow updates', 1
> reconfigure with override
> go
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <Nick@.discussions.microsoft.com> wrote in message
> news:BB79FB0A-8BED-41D9-BE60-E539DCCFD9D7@.microsoft.com...
> name='nickDistribtuion'
> administrator
>
>
where i try to delete it . a error message occured as below:
Error 3724: cannot drop the database because it is being used for replication
please help
Cheers
nick
back up your master database. Issue the following query:
UPDATE master..sysdatabases SET category = 0 where name='distribution'
delete the distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:E112AE00-DABA-4FB1-BE70-0CAA0791D62F@.microsoft.com...
> after i disable replication. the distributionDB is still there.
> where i try to delete it . a error message occured as below:
> Error 3724: cannot drop the database because it is being used for
replication
> please help
> Cheers
> nick
>
|||when i tried to run
UPDATE master..sysdatabases SET category = 0 where name='nickDistribtuion'
i got error as below:
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
"Hilary Cotter" wrote:
> back up your master database. Issue the following query:
> UPDATE master..sysdatabases SET category = 0 where name='distribution'
> delete the distribution database.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <Nick@.discussions.microsoft.com> wrote in message
> news:E112AE00-DABA-4FB1-BE70-0CAA0791D62F@.microsoft.com...
> replication
>
>
|||sp_configure 'allow updates', 1
reconfigure with override
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:BB79FB0A-8BED-41D9-BE60-E539DCCFD9D7@.microsoft.com...
> when i tried to run
> UPDATE master..sysdatabases SET category = 0 where
name='nickDistribtuion'
> i got error as below:
> Ad hoc updates to system catalogs are not enabled. The system
administrator[vbcol=seagreen]
> must reconfigure SQL Server to allow this.
> "Hilary Cotter" wrote:
|||it works. thanks a lot.
Nick
"Hilary Cotter" wrote:
> sp_configure 'allow updates', 1
> reconfigure with override
> go
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <Nick@.discussions.microsoft.com> wrote in message
> news:BB79FB0A-8BED-41D9-BE60-E539DCCFD9D7@.microsoft.com...
> name='nickDistribtuion'
> administrator
>
>
How to Delete Data on the Subscriber but Maintain It on the Publisher?
Hi,
We're using merge replication between a Sql Server 2000 Publisher and
Subscribers on PocketPCs running Sql Server Ce.
One part of our data sync scheme deals only with newly created data on
the PocketPC. For this we have an upload-only replication that sends
the new data from the device to the Publisher.
Due to storage constraints on the PcketPC, we want to delete the data
from these SQL CE tables after it has been sent to the Publisher, but I
am assuming that if we do so, upon the next replication this data will
then be deleted from the Publisher also.
We want to avoid that from happening - we want to delete the data on
the subscriber after it has been merged, but we also want to maintain
the data on the publisher.
Is there any way that we can delete the data from the subscriber but
still maintain it on the publisher.
Thanks,
JJ
create a sql account which has rights to insert and update, but not delete
on your publisher. Add this to the pal, and use this account in your SQL CE
program in the PublisherLogin parameter.
Now, for the articles which you wish the deletes not to make it to the
publisher, right click on the publication, select publication properties,
and then click on the articles tab. Click on the browse button to the right
of your table names, and select the merging changes tab. Ensure the delete
command is selected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JJ" <jjabour@.gmail.com> wrote in message
news:1127612785.479827.68040@.o13g2000cwo.googlegro ups.com...
> Hi,
> We're using merge replication between a Sql Server 2000 Publisher and
> Subscribers on PocketPCs running Sql Server Ce.
> One part of our data sync scheme deals only with newly created data on
> the PocketPC. For this we have an upload-only replication that sends
> the new data from the device to the Publisher.
> Due to storage constraints on the PcketPC, we want to delete the data
> from these SQL CE tables after it has been sent to the Publisher, but I
> am assuming that if we do so, upon the next replication this data will
> then be deleted from the Publisher also.
> We want to avoid that from happening - we want to delete the data on
> the subscriber after it has been merged, but we also want to maintain
> the data on the publisher.
> Is there any way that we can delete the data from the subscriber but
> still maintain it on the publisher.
> Thanks,
> JJ
>
We're using merge replication between a Sql Server 2000 Publisher and
Subscribers on PocketPCs running Sql Server Ce.
One part of our data sync scheme deals only with newly created data on
the PocketPC. For this we have an upload-only replication that sends
the new data from the device to the Publisher.
Due to storage constraints on the PcketPC, we want to delete the data
from these SQL CE tables after it has been sent to the Publisher, but I
am assuming that if we do so, upon the next replication this data will
then be deleted from the Publisher also.
We want to avoid that from happening - we want to delete the data on
the subscriber after it has been merged, but we also want to maintain
the data on the publisher.
Is there any way that we can delete the data from the subscriber but
still maintain it on the publisher.
Thanks,
JJ
create a sql account which has rights to insert and update, but not delete
on your publisher. Add this to the pal, and use this account in your SQL CE
program in the PublisherLogin parameter.
Now, for the articles which you wish the deletes not to make it to the
publisher, right click on the publication, select publication properties,
and then click on the articles tab. Click on the browse button to the right
of your table names, and select the merging changes tab. Ensure the delete
command is selected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JJ" <jjabour@.gmail.com> wrote in message
news:1127612785.479827.68040@.o13g2000cwo.googlegro ups.com...
> Hi,
> We're using merge replication between a Sql Server 2000 Publisher and
> Subscribers on PocketPCs running Sql Server Ce.
> One part of our data sync scheme deals only with newly created data on
> the PocketPC. For this we have an upload-only replication that sends
> the new data from the device to the Publisher.
> Due to storage constraints on the PcketPC, we want to delete the data
> from these SQL CE tables after it has been sent to the Publisher, but I
> am assuming that if we do so, upon the next replication this data will
> then be deleted from the Publisher also.
> We want to avoid that from happening - we want to delete the data on
> the subscriber after it has been merged, but we also want to maintain
> the data on the publisher.
> Is there any way that we can delete the data from the subscriber but
> still maintain it on the publisher.
> Thanks,
> JJ
>
How to Delete article in mearge replication
Dear Friends
I want to delete one article from the current Merge
replication i have use the sp_dropmergearticle but it is
comming up with the error that the ARTICLE CAN NOT BE
DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
APPLIED.
Please suggest how I can do the same.
Thanks and best regards
Sharad
Kick everyone off your subscribers and publisher. drop the article. Recreate
your subscriptions using the no-sync option.
Run all merge agents.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:0cea01c4f3f0$9e1da4e0$a601280a@.phx.gbl...
> Dear Friends
> I want to delete one article from the current Merge
> replication i have use the sp_dropmergearticle but it is
> comming up with the error that the ARTICLE CAN NOT BE
> DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
> APPLIED.
> Please suggest how I can do the same.
> Thanks and best regards
> Sharad
|||Sharad try with this SP
sp_repldropcolumn
David Barquero
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:0cea01c4f3f0$9e1da4e0$a601280a@.phx.gbl...
> Dear Friends
> I want to delete one article from the current Merge
> replication i have use the sp_dropmergearticle but it is
> comming up with the error that the ARTICLE CAN NOT BE
> DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
> APPLIED.
> Please suggest how I can do the same.
> Thanks and best regards
> Sharad
I want to delete one article from the current Merge
replication i have use the sp_dropmergearticle but it is
comming up with the error that the ARTICLE CAN NOT BE
DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
APPLIED.
Please suggest how I can do the same.
Thanks and best regards
Sharad
Kick everyone off your subscribers and publisher. drop the article. Recreate
your subscriptions using the no-sync option.
Run all merge agents.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:0cea01c4f3f0$9e1da4e0$a601280a@.phx.gbl...
> Dear Friends
> I want to delete one article from the current Merge
> replication i have use the sp_dropmergearticle but it is
> comming up with the error that the ARTICLE CAN NOT BE
> DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
> APPLIED.
> Please suggest how I can do the same.
> Thanks and best regards
> Sharad
|||Sharad try with this SP
sp_repldropcolumn
David Barquero
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:0cea01c4f3f0$9e1da4e0$a601280a@.phx.gbl...
> Dear Friends
> I want to delete one article from the current Merge
> replication i have use the sp_dropmergearticle but it is
> comming up with the error that the ARTICLE CAN NOT BE
> DROPED AS THE SNAPSHOT OF THE SAME PUBLICATION IS ALREADY
> APPLIED.
> Please suggest how I can do the same.
> Thanks and best regards
> Sharad
Subscribe to:
Posts (Atom)