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

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

Want to set replicate schema changes on a merge publication to false - any problems

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

Is it safe to change the replicate schema property of a publication mid-stream? We have a server that rarely gets schema changes to the published table, but once a year or so the vendor might change the underlying table(s) in the publication. This is usually a field size change and/or adding a new field.

I don't want to take any chances on disrupting the current merge publication since hundreds of users are already syncing their subscriptions daily. And the sqlce database file is very large (1gb) so it's not practical to have them all reinitialize their subscriptions.

The application using the replica databases will not need the majority of these changes, but at least one of the fields that is being altered in size is already part of the publication. Am I right in assuming that this will 'break' the subscriptions anyway?

View Complete Post

More Related Resource Links

Subscriber data changes problems when use_partition_groups set to false

Hello,Within our solution we use merge replication to replicate data between MS SQL Server 2008 (running on MS Windows Server 2008) and MS SQL Server Compact 3.5 (running on Windows XP Tablet PC). Initially we set the parameter @use_partition_groups of sp_addmergepublication to true. The replication worked well but later we have observed problems while inserting/updating records on publisher. (commands have been timed out) The timeout problem has been solved by setting @use_partition_groups to false - the performance on subscriber is good, but (as we later realized) subscribers are not getting the changes properly (Publisher’s deletes are not reflected at subscriber) We have found following thread however the answer did not help us: http://social.msdn.microsoft.com/Forums/fi-FI/sqlreplication/thread/638bc057-b12f-4e04-b42d-564b244c715fWe would need to solve this problem ASAP.Please help.Thx a lot in advanceRobert

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.

SQL Server 2005 Merge Publication Filters

Is it possible to have Merge Subscriptions of a Publication (or publications) storing a different range of data.E.g. Sub1 contains the clients of one Sales Person only.Sub2 sontains the Clients of Many Sales Persons in one Division.Can you do this with one Publication?  How would you setup the filters for this then?Or do you have to have two separate publications of the same database.Pub1 filtering based on the SalesStaff ID = SUSER_NAME()Pub2 filtering based on the DivisionID=SUSER_NAME()Can someone describe how best to setup this scenario?

Problems with Schema Collection and Namespaces


Our company is going to start exchanging XML documents and I'm trying to understand how to correctly use XML data types in SQL Server 2005.

There is a published xsd which I think I'm supposed to store in a Schema Collection so that Sql Server can use it to validate typed XML variables and columns.
There also are some examples XML documents available for testing.

Because the xsd and the samples are relatively huge (1-2 megabytes each),  I have distilled both down to the minimum necessary fields both for my own sanity while testing and for use in examples to forums such as this.

I believe I am down to my last problem which centers on understanding namespaces.

The actual XML documents do not and will not have any namespace parameters within them.
But I am only able to succesfully validate my testing samples when I include an xmlns parameter.
What am I doing wrong?
How can I get a sample without an xmlns parameter to successfully validate?

Here is what I have:

USE LSExport

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE [name] = 'MyPrivateSchemaCollection')
 DROP XML SCHEMA COLLECTION dbo.MyPrivateSchemaCollection

DECLARE @testSchema XML
SET @testSchema =
'<?xml version="1.0" encoding="UTF-8"?>

Merge replication do not replicate an article itens


Hello people,

Please, I need some expert help. I´ll show you the trouble scenario:

I have a publiser and some subscriber to this plublisher.

This publisher publicate some articles that are filtered by the subscriber id, and other articles that are filtered by the other article id .


  Article A - generic data

  Article B - data restrict to each subscriber


Article A - select * from article_A where article_A_id in (select article_id from article_b where article_id = HOSTNAME)

Article B - select article_id from article_b where article_id = HOSTNAME

When it´s sincronized, only article B values are replicated to the subscriber, and the article_A values do not replicate. (if I do the select at the publisher database the roles are showed)

I have changed the processing_order property like are showed here http://msdn.microsoft.com/en-us/library/ms147310%28v=SQL.90%29.aspx , but the article_A values do not replicate.


Somebody already are passed for this situation, and can show me the light?











table schema changes not being propagated - merge replication


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

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

The question of Merge Publication


We are attempting to setup a master republisher model for horizontal
scaling of our mobile devices. We currently have what will be the
republisher with CE's connected to it. The goal being I do not want
to cause re-inits and data loss from the CE's to the republisher when
I add the master.

What I have done is take a backup of our republished data and put it
on the master. So I have the exact same schema/data in both my
republisher and my master. Life is good, I setup the Master
publication with @pre_creation_cmd = 'none' (If the table already
exists at the Subscriber, no action is taken) this is so it will NOT
drop the tables and recreate them. I already have the data in place
and I just want to replicate the changes. The problem I am having is
once the master to republisher script is in place and I try to sync a
CE, the changes are NOT replicated to the master. It is like since I
said pre_creation_cmd = none, it does not care what changes anymore
for the data that already existed.

Any one can fix this problem?

sql 2005 merge replication replicate 65 GB binary and cause "MSSQL_REPL20216 due to OS error 2"



We're using sql 2005 ent merge replication (SP2) and replicate 65GB binary data. However, after merge job running around 60 hours and nearly finished, it has the error messages like this :

From distribution.msrepl_errors and distribution.msmerge_history:

"The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an outfile to write" (Error Number: MSSQL_REPL-2147201001)

"The process could not read file "E:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\IMDNACMPS33_MPSERVER_MPSERVER\20110411220596\NoticeInfo_2.cft" due to OS Error 2(Error Number: MSSQL_REPL20216)


Please note that the above messages kept continuously appear in both event viewer and system tables between 1 minutes. Any suggestions or help to fix this?? Do I need to click 'stop synchronizing' and click 'start synchronizing' to restart the merge job again or click 'reinit subscription'? An urgent response is indeed necessary for this case. Thanks a lot.

Thanks and regards,

P.S: for your reference please find the attached error photo

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.



SQL Server 2005 RMO Merge Replication Problems


We have a Visual Studio 2005 Windows Forms Application with SQL Server 2005 on the servers (Windows 2003) and SQL Express 2005 on the client PCs (Windows XP) using Merge Replication (Pull Subscriptions).

We are planning to upgrade user PCs to Windows 7 and are performing tests, we have installed SQL Server Express 2005 SP3. But when we try to synchornize, the error message we get is

 "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=,Culture=neutral,' or one of its dependencies. An attempt was made
to load a program with an incorrect format."

What would be the best approach to fix this problem ? We are not planning on upgrading the servers now.

Is there a way to change "Microsoft.SQLServer.Replication" DLL version that we referrence in the Visual Studio 2005 project so it can work with both XP and Windows 7 users without causing any problems ?

Your help would be highly appreciated.

Thank you,



Top .NET Performance Problems and how to avoid them

Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

Visual Studio 2008 Memory problems


My memory in task manager reaches about 900,000K  and I don't know why it does this. Definately slows everything down, especially when I rebuild my tableAdapters, takes about 30 seconds sometimes to rebuild the project.

AutoGenerateSelectButton="False" ignored when using Domain Service


Crosspost from RIA Services forum: http://forums.silverlight.net/forums/t/196466.aspx

I'm creating a Dynamic Data application in VS2010 and have recently switched to using a Domain Service to give greater control over the data presented to the client. I've noticed that the AutoGenerate<Action>Button attributes on my GridView are being ignored. The presence of the Edit and Delete buttons appear to be contingent on the existence of Update and Delete methods on the partial classes autogenerated in the Domain Service, but i'm not seeing how to control the generation of the Select button. Is there a way to control this from within the Domain Service class?

Problems with Forms Authentication in DD 4 site


Hello,  I am seeing a strange problem with Forms Authentication in my DD site.   A user logs into and can view/edit/delete data all day, but when they execute a Custom Filter against data (for example , a control DynamicData/Filters/CustomerLastNameSearch.ascx ) then the site auth fails, and redirects to the log in screen.

in web.config I have

     <authentication mode="Forms">
            <forms name=".Star" loginUrl="~/Login.aspx" protection="All" defaultUrl="~/Default.aspx" path="/" timeout="43200" cookieless="UseCookies" />     

Offhand, I am thinking two things : that DynamicData/Filters path requires some special handling for some reason, or the control extension ascx is causing auth to get confused.   Has anyone else experienced this or have any suggestions?  Thanks!

How to extract a schema from a sql server database



  I need to extract or get the schema from tables in my databse and what is the best way to do it. Is there any third party tool to do it??


Any idea will be appreciated..



Problems posting to a package


Hey Y'all

I'm currently creating a program that accesses a mysql package to add data to a few tables.  I'm currently getting an error that says  "

ORA-01403: no data found
ORA-06512: at "JESSE.PKG_WORKPLAN", line 42
ORA-06512: at "JESSE.PKG_WORKPLAN", line 177
ORA-06512: at line 1


These lines are


and 177:add_team_members(V_PROJECT_ID, V_START_DATE, v_FISC_YEAR, V_MEMBERS ,V_NUM_MEMBERS,'add');

Checking these variables in my asp.net page they are all receiving parameters.

Is there a  way to see what the database is actually receiving from the asp.net page?

Or is there another thing to be looking at?


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