.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

Dropping a column in publication without affecting transactional replication

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server
I have set up Transactional Replication ONLY from database A  in server 1 (Publisher) to Database B in server 2 (Subscriber). I didnt set up snapshot replication but the database B was restored after taking backup of database A. Replication is fine now. But, I need to drop one column which is a primary key in one table(foreign key in another table :)) in Publisher without causing any issues to the replication. BTW, I am using SQL SERVER 2008 R2. Can someone please tell me how to do this?

View Complete Post

More Related Resource Links

transactional replication with identity column


Dear friends,

I have a transactional replication between A and B where A replicates its data into B . the schema of A and B are EXACTLY same. I have a table "t" in both databases which has an IDENTITY column with name "c" and it is set to be NOT FOR REPLICATION in both databases. Indeed I have "A.t.c " and "B.t.c " columns. Also I should mentioned that I never do any changes in B.t table. It should just get the last data from A.t (but because of some reasons I need to have a same schema).

I do the following steps :

1- Add a new row to A.t . (and see the added row in B.t after a few seconds either )

2- update this added row's c2 column (not the c which is  the identity and PK column)

3- an exception I receive in Replication Monitor which says I cannot update the B.t.c column because it is an Identity column!!!... (I checked the related MS's related update stored procedure)...

the question is why it wants to update the Identity column which is not needed indeed!?

Dropping a foreign key column in replication


I have set up transactional replication. I want to drop one column which acts as a foreign key in one of the tables in publisher. If I drop that column, will that drop be reflected in the subscriber??? BTW, can I drop that column without any issues?? If not, what are the steps to be followed for dropping that column?? Can some one please help me.....

Transactional Replication Identity column difference in SQL 2008


Hello All,

I have a Pull Transactional Replication in SQL 2000.

To enable the copying of Identity columns, my subscriber Insert stored procs are modified to mark


In the end, I set it to Off.

The environment is stable for 2 years. Now we are upgrading to SQL 2008.

Do I still need to do the above step?

I have noticed that SQL 20088 is even updating Subscriber table for schema changes also.

I am not sure if MS took care of that issue in SQL 2008.

Please let me know your inputs.



Changing the column width in Transactional Replication

I changed  the column width on the publisher and all of sudden my replication failed saying unable to insert identity value.And finally I had to remove the article from the publisher.
How can I increase the column width of table without any impact  thats published as article in Transactional Replication?

Issue while Adding article in existing publication Transactional Replication.


We are facing difficulties adding article to an existing publication.The replication was setup by restoring the backup on subscriber  from publisher and changed the setting Intilialize from Backup =True

Used the following steps but it almost bought down the system.




@publication = N'marcus8rpl',

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 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

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.

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

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?

Transactional publication with updatable subscriptions in SQL Server 2005, SvPk 2

Subscription to "Transactional Publication with Updateable Subscriptions" works only one way.  Changes take effect on subscriber, but the subcriber is unable to update data on publisher.   I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.'  All agents are running at the Distributor (Publishing Server.)   The subscriber is unable to connect to the Distributor using the SQL Server login.    Following is the error message I get:   Creating Subscription(s)... - Creating subscription for 'SQL3' (Warning) Messages Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------MSDTC on server 'SQL3' is unavailable.Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476   Please suggest procedure to end this dilemma.  The link has no info available.

XML Column containing single quote causes replication error

We have SQL Server 2005 transactional replication set up, and some of our tables have XML datatype columns.  We have run into an issue with the following error in the publication: Incorrect syntax near 's'. (Source: MSSQLServer, Error number: 102) Get help: http://help/102 Looking at the distribution database, I was able to extract the offending command, and it turns out that the XML column has a single quote (') embedded in it, and the command that was generated for inserting the row into the subscriber table has bad syntax because of the single quote (i.e., it is not escaped).  One interesting note is that the single quote happens to also show up in a non-XML column, and the command segment for that column was generated correctly (it was escaped). Is there a workaround / solution for this?

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!

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.

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  

Row Filters using Transactional replication & joined criteria

Hi guys, I am after some advice as to my replication setup and what I can do with it.  I have only basic knowledge of replication but have had transactional replication setup for a while now which replicates a few of the main databases tables and it is working fine.  However we want to improve performance of our subscriber database, so I would like to use row filters for this. Imagine the setup of a database table for Questionnaires which has a Void bit field in it, and a table for Answers.  On the subscriber I want only non-void Questionnaires and their answers. So I set the row filter for the Questionnaire table to be "WHERE Void = 0", I think this works.  However, I don't think it works when I am working on the Answers table, my row filter would be "WHERE QuestionnaireID IN (SELECT QuestionnaireID FROM Questionnaires WHERE Void = 0). This works fine for the initial snapshot but not for following transactions. For example, if I made a Questionnaire not void then I'd expect that to make its way to the subscriber along with its answers. Or if I voided a Questionnaire I'd expect that Questionnaire and set of answers to be removed from the subscriber database. I have read that this type of filter that uses a subquery only works with merge replication, if my understanding is correct? So my question here is, can you validate my findings, is

Transactional Replication: Agent Profiles

Can someone explain the different Agent profiles and when is it appropriate to use one over the other?  Agent profile, continue on data consistency error...when can this be used?
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