.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

avoid accidental delete on Subscriber tables - transactional replication

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

Hello pundits..

I am looking to see if we have any way to set on the subcsriber to avoid accidental deletes on subscriber tables to avoid the 20598 error(where the publisher is trying to replicate a record not existing(rather deleted in accident) at the subscriber end). This is for transactional replication


View Complete Post

More Related Resource Links

transactional replication and sp_MSupd tables

Hi thre, My target server keeps running sp_MSupd sp for different values in profiler and synch status shows "delivering replication commands" I keep waiting but this never ends. and ultimately we run a snapshot and it can't be applied. So recreate the whole replication again. It runs fine for a while but again, the sp_MSupd sp starts running on the target server and the replication goes down again. I don't know how to fix this. any advices?   Regards

Restoring Transactional Replication on the subscriber


We have a situation were  when the subscriber breaks for some reason we need to resume the replication  once the subscriber is fixed. Is there any way we can accomplish this withoout the running the snapshot all over again.?If yes let me what are the steps

Note: We are performing 15 min Log backups on the publisher.

Restart the server that has been configured for subscriber in a transactional replication


Whats the impact on restarting the server that has been configured for subscriber in a transactional replication.

The distributor and the publisher have been set on different servers.

Transactional Replication ISSUE - 487 tables need to be replicated


Hi All!

I have configured transactional replication on 7 databases from an sql server 2005 to another sql server 2005.

One of them has 487 tables and when I check "Replication Monitor" it shows that everything ok, but database above , that shows the message "Performance Critical".

My question is : The tables have been replicated or not ? How can I check this ? It´s not possible check table per table. Anyone suggests any other way to check if tables and their data have been replicated.

Thanks in advance ,



Locking published tables while generating the snapshot infinite (Transactional replication)


I set up a transactional replication, publisher 2005 SP2 to a subscriber SQL 2008r2. The database is about 90 GB. When I want to create a Snapshot, the snapshot agent is running to 96% and then give a error. I have set up some logging, here is the tail of it:


2011-05-16 20:16:15.32 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLAutorisatieRegister' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.32 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLBericht' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLBerichttype' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLFault' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$Ziekmeldingsregistratie' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$Ziekte Bijzonderheden' during concurrent snapshot into the distribution database

Sql Scripts - Delete all Tables, Procedures, Views and Functions


In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product. 

I managed to find these scripts which should help you clean out your database.

Use at your own risk.


Delete All Tables

--Delete All Keys


merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Transactional replication: Updates to one of my columns (sometimes) does not get replicated from the

hi, This is my scenario: I have a varchar(max) column on a table that is replicated to other servers. I have a stored procedure that does and update to this column and other columns on the table. This is done inside a transaction that also includes other updates. When I call the stored procedure, this one varchar(max) column's value is not replicated. When I just run the single update statement on its own, the value is replicated. Anyone know something about this? Thanks!    

transactional replication falling behind. millions of transactions in sp_brosereplcmds

Hi, Hi All, we have transactional replication set up with all the agents running at source database. The synchronization status is stuck at "delivering replication transactions". The replication latency in replication monitor says excellent. when I check sp_browsereplcmds. it ever ends. It has over more than millions of rows of output. We were having trouble with this replciation recently because at the source db. they run a large batch. So we decided to schedule snapshot agent to run exactly after that batch but the snapshot doesn't seem to helping out with the latency. what should be done to resolve the issue. Please help. thanks.

getting an e-mail notification when a transactional replication stucks

Hi all, I've SQL Server 2005 and a transactional replication scenario with one puplisher and 11 subscribers. Sometimes one of the subscriptions fails and a manual operation is required. Is there a way to get an e-mail notification whenever the replica fails or stucks or simply degrade its performances?

Issue with transactional replication on SQL 2005 SP3 using row filters with bit columns

We recently upgraded from SQL 2000 to SQL 2005 SP3 and I am noticing something that I think is a bug with replication from a publication that has an article in it with row filters.  It seems to be that if the row filter has columns that are bit datatype and that column is updated on the publisher to cause the row filter to exclude it, it is not removed from the subscriber.  If you update a column that is part of the row filter that is not a bit datatype it works as expected.  I can reproduce this in a test environment with a small base table.  In production this is resulting in an occasional 20598 error "The row was not found at the Subscriber when applying the replicated command". Is anyone aware of a hotfix that addresses this? I can think of a few work arounds but the table is rather large and I really would like to avoid having to pull replication, re-snap or changing datatypes on the base table etc. Any suggestions are greatly appreciated.  Thanks!

replication; what is: SqlCeReplication.Subscriber?

I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC.  In my code I have a SqlCeReplication object.  One of the properties that must be set is: Subscriber, but I have no idea what that should be set to.  When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber".  Any help will be appreciated.

set reinitialization period on Transactional replication

How do you set the parameters on Standard Transactional Replication to Reinitialize after a certain period of time? Thank you.

Delete from multiple tables

 After trying myriad ways to accomplish deleting from two table, I found a way that actually works. However, AM am sure there is a better way to do this. Any ideas?  Imports System.Data Imports System.Data.SqlClient Imports Telerik.Web.UI Imports System.CodeDom Imports System.Web Partial Class Default2 Inherits System.Web.UI.Page Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sql As String Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("CF_SQL_Connection").ConnectionString() sql = "DELETE FROM Table1 WHERE IDTable1 = '5'" Dim connection As New SqlConnection(strConnString) Dim command As New SqlCommand(sql, connection) command.Connection.Open() command.ExecuteNonQuery() command.Connection.Close() Dim sql1 As String Dim strConnString1 As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("CF_SQL_Connection").ConnectionString() sql1 = "DELETE FROM Table2 WHERE IDTable1 = '5'" Dim connection1 As New SqlConnection(strConnString) Dim command1 As New SqlCommand(sql1, connection) command1.Connection.Open() command1.ExecuteNonQuery() command1.Connec

Unable to publish two tables for replication. They have a key with a red x.

I am trying to figure out why I can't access the DB that I've replicated.  Two tables have a key with a red x that makes it so I can't publish them for replication.  In addition, how do I replicate all the user roles and permissions with the database that is being replicated?  I am using SQL Server 2000.   More info:  I can't access the replicated DB through the client software.  It is accessible through SQL Management Console.  I had to manually export all users and roles and then run the query to get them into the replicated database.  Is there a more thorough way to replicate a database?  How can I make an exact copy on a different server so all information is intact?

Transactional Replication: Schema Option

1.I need to Replicate a db without replicating its constraints, only the tables. What is the best way to achieve this? 2.Need the Subcription(replicated db) to have the collation of the server being replicated to. Please explain how the options need to be configured. Thanks  

linq - delete two tables not work

BurnbetDataContext db = new BurnbetDataContext(); var q = (from c in db.GetTable<tbl_Payment>() where c.UserId == "fc44c835-8e80-45ee-af84-68339a09c053" select c).Single<tbl_Payment>(); db.GetTable<tbl_Payment>().DeleteOnSubmit(q); db.SubmitChanges(); In App_Code I BurnbetDataContext.I have two tables and tbl_PaymentDetails tbl_PaymentAssociation is:Parent Child Class -->>> UserId UserId ClassData in Table tbl_PaymentDetails (child) does not delete.      
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend