.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

table schema changes not being propagated - merge replication

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

I have attemped to change column types (i.e. from int to varchar(8)) on read-only published tables. I used ALTER TABLE ALTER COLUMN to make the change, and according to BOL this should make sure the changes are seen at the subscribers. Unfortunately, after I make the change to table and run sp_enumeratependingschemachanges, I do not see the table changes listed. However I can make changes to SPs and UDFs, and they do show up in sp_enumeratependingschemachanges and on the subscriber. Any ideas would be appreciated.
The publication is marked for replicating schema changes, the publisher is SQL 2005 and the subscribers is SQL 2005 Express btw.


Darrell Young

View Complete Post

More Related Resource Links

Merge replication unique id of table

Hi guys,I am designing a CRM solution using SQL Server 2008 and windows forms.The solution should allow people to work in a disconnected mode.I would like to ask for an advice about the best practice for implementing the solution.Currently I am expecting to have 1 central server with about 20 small clients.When the connection between the server and the clients falls the clients should work in disconnected mode persisting all changes to a local database. So on the Server I will use SQL Server 2008 Enterprise and for the clients SQL Server Express.I am planning to configure merge replication between the server and the clients and have the clients operate against the server until the connection drops.One question that I have in particular is what to use for ids of my tables ? I am currently using identity columns and planning to assign a range to each client and server so that ids do not overlap.But I am wandering whether not to use custom id for example a varchar(15) for example. Then I can easily assign a prefix for each client and ensure uniqueness of ids. I have also read that SQL Server adds a GUID to each table anyway so I wandered if using a GUID is a good idea ? I appreciate your answers.Regards,kzmp

Merge replication - table with geography data type



I have a problem with geography data type replication. Any change that is done on a Geography column breaks the replication - the error message is "The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber." I double checked compatibility settings that is suggested here: http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/d8e31621-5d43-48cd-bdd3-c02b8a24625c. But both publisher and subscriber are 100RTM.

I checked synchronization logs and found a strange message - attempt to convert varchar to geography. I don't understand from where the varchar data type comes from. Tables at subscriber and publisher has the same format - geometry stored in geography data type.

2010-09-20 18:36:22.499 The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.
2010-09-20 18:36:22.845 Category:NULL
Source:  Merge Replication Provider
Number:  -2147199398
Message: The

Sanpshot Replication of all objects and Table Schema but no Data


Can this be done? I just want all database objects to be pushed out to a subscriber once a week via snapshot replication. Procs, tables, triggers, functions etc but I dont want any of the table data

Thanks in advance

SQL Server 2005 Schema Replication in Merge Replication


Dear Sir,

I am using SQL Server 2005 Developer Edition for Creating & Testing Database.

I had created Merge Publication WITHOUT FILTERING ROWS and Created Push or Pull Subscription. Now if I Add or Drop column using ALTER TABLE (T-SQL) in Table OR modify stored procedure using ALTER PROCEDURE in Published Database then while Synchronizing It replicate same in Subscriber Database and works very fine.

But when I had created Merge Publication WITH FILTERING ROWS (Parameterize or Static) and Created Push / Pull Subscription and I try to Add or Drop column using ALTER TABLE (T-SQL) in Table  OR modify stored procedure using ALTER PROCEDURE in  Published Database then while Synchronizing It DOESN'T REPLICATE in Table of Subscriber Database but tries Replicate in Stored Procedure and display error.

After Adding Column in Table of Published Database, If I Checked Article in property of Publication then it Adds column but it doesn't selects it. I also checked using sp_enumeratependingschemechenges it displays pending schema changes of stored procedure but

Merge Replication - Add table to existing publication


I added a table to an existing publication and then generated a new shapshot.  The merge agents then sent the new table and data down to the sunscribers as planned.  The issue is that at the next cycle the merge agent re-initilizes the table again.  This happens over and over again.  If I stop and restart the agent I can watch it send the schema back down to the subscriber.  Any thoughts are appreciated.



Is there no need to manually clean up merge replication system table?



In my previous post, Hilary replied that the size of MSmerge_Contents doesn't really matter, but index fragmentation does. However, referring to BOL, it says, "Additionally, you should minimize the size of the merge system tables (specifically MSmerge_history) by using sp_mergecleanupmetadata." Is it still necessary to do that in SQL Server 2005? 

Also, when running select metadatacleanuptime from sysmergesubscriptions, I found that the cleanup time is just about a week before. However, my merge interval is only 5 minutes, and I remembered during last week I did a manual sp_mergemetadataretentioncleanup although paused in half way. How to check the clean up interval?

Last but not least, I have already did index rebuild on MSmerge_Contents, MSmerge_henhistory, and MSmerge_tombstone, and found that the average fragmentation dropped from above 90% (MSmerge_Contents) to below 1%, but the overall performance did not improve obviously. I remembered when merge replication was set up at the begining, th

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?

Steps by Step document for configuration Merge Replication in 2005

Hi , Can anyone provide link to configure Merge Replication in 2005 with print screen.Also let me know the difference between Transcation level and Merge Replication. regards Vijay  

SQL Server 2005 Merge Replication

We did the following: We setup a database with all of standard tables, views, etc. We then put in place merge replication for a few of the tables within this database, including a tables called "Areas". We can execute SQL statements to insert rows into this table fine. We then applied an update script for this database to change a few tables by adding fields and changing indexes, etc.  We didn't change the "Areas" table though. After the update to the database structure, we get an error when trying to insert into this "Areas" table. The error message was the following: The insert failed. It conflicted with an identity range check constraint in database 'AETest', replicated table 'dbo.Areas', column 'AREPrimaryId'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.  The statement has been terminated.   What would cause such a problem and how do we resolve it?  The structure of the "Areas" table before and after the update is the following: USE   [AETest] GO /****** Object: Table [dbo].[Areas] Script Date: 08/24/2010 17:42:36 ******/ SET   ANSI_NULLS ON GO SET   QUOTED_IDENTIFIER ON GO SET   ANSI_PADDING ON

Merge Replication: How to give read access on snapshot share to a sql account

Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

fragmentation in merge replication

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?

Merge Replication: Fails using Replication.SaveProperties when changing Subscription Info

I'm trying to use SQL Server/Compact Framework 3 Merge Replication functionality and am running into a problem when calling Replication.SaveProperties (using c#). I was hoping to get some clarification on whether its my interpretation on how things should work vs a coding problem. Basically, the Merge Replication seems to work as I can sync information between my handheld and server databases.  However, I've created a form on my handheld so that the user can change some synchronication settings (e.g. Publication or Publisher Name) and then save it.  What I've noticed is : 1) You can NOT call Replication.SaveProperties until after a synchronization is performed (otherwise you get an error message).  Assuming this is so that it can create the "__sysMergeSubscriptions" table. Does this sound right? 2) If I then try to change the saved Subscriber information via another call to Replication.SaveProperties (e.g. change the Publication info), I then get an error message that says: The subscription was not found. Publisher, PublisherDatabase, or Publication  could be incorrect. Is this normal behavior?  Does this mean that you can you NOT change subscriber information; only add or drop it? Thanks,  Ed

configuring merge replication

Hi ALL,              I am configuring merge replication... For example Publisher A sql server 2008 ( databse adventureworks) subscriber B sql server 2000 ( database adventureworks) I already have database created at subscriber....both databases at publisher and subscriber are identical in structure but changes in data( at subscriber there some inserts and updates) now before i create new snapshot and applied to subscriber i want to upload changes which are at subscriber... how can i apply changes to publisher database before i run merge agent... TIA

Could not complete cursor operation because the table schema changed

Microsoft SQL Server  2000 - 8.00.2039 Got this error: Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000 Is this a known issue?  I suspect the application logic may cause this error. Please advise. Thanks a lot!  

Merge Replication, Push Subscription : The snapshot takes centuries to apply

Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

error in merge replication

Hi ALL,      I have merge replication configured between republishers( sql 2008) and subscribers( sql 2000)...i am getting the following error while trying to sync   The merge process could not update the list of subscriptions. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201010) Get help: http://help/MSSQL_REPL-2147201010 Cannot insert duplicate key row in object 'dbo.sysmergesubscriptions' with unique index 'uc1sysmergesubscriptions'. (Source: MSSQLServer, Error number: 2601) Get help: http://help/2601 The subscription could not be created. (Source: MSSQLServer, Error number: 14057)   Any suggestions y i am getting this error TIA

Merge data from two different databases with the same schema

I am writing an iPhone web app for tracking business mileage. The app uses AJAX and HTML 5 manifest caching to enable offline use. The data is stored on the server in a SQL Server database with the following schema:  When there is no network available, the app stores new data in a local database with the same schema using SQLite on the iPhone. Once a connection is aquired, the app automatically uploads the new data to the main database for permanent storage. Now, here's the question: I have data from two databases with the same schema. How do I merge the new data into the main database using LINQ? The primary key IDs in the new data must be discarded and regenerated by the main database so that there are no ID conflicts, but the two relationships must be maintained properly. What is the easiest way to do this? Any help would be greatly appreciated.
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