I have an application that uses web-based merge replication.My publisher is SQL 2005 and my subscriber is SQL 2005 Express.I control the replication with RMO code.If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases.However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio.What would cause these changes to not be recognized?Any ideas would be appreciated.
Hi there,
If you have changed the data in such a way as to stop the triggers from firing then these changes wouldn't be replicated.
This is normally associated with a bulk insert of data using something like a bulk insert but it doesn't sound like you are doing that.
To check if the triggers are firing you could check for the existence of the rowguids in MSMerge_Contents for the rows that you have changed.
|||
Merge replication uses triggers to track changes. First thing to check is if your updates are causing the triggers to fire and the transactions are commiting after updates. Is there any way for you to profile the updates made by your app? Do you know what DMLs your app uses to make the changes? If you can confirm that the triggers are firing then the next thing to check is if replication is picking up the changes. Are there any errors returned by replication? You can look at the replication log file for detailed errors. Replication log file is sqlmergx.log and its default location is %program files%\microsoft sql server\90\com.
thx
Sudarshan
|||
JR-J
Thanks for your resopnse.
I checked the MSmerge_Contents table after making changes to one of my application's tables. There was a row added when I made the changes from within Management Studio, but not when I made a similar change from within my application. So it looks as though my application doesn't cause the triggers to fire. The changes appear in the application's table in both cases.
I'm just using simple INSERT, UPDATE and DELETE statements in stored procedures in my application. No bulk inserts.
|||
Sudarshan,
Thanks for your resopnse.
I checked the MSmerge_Contents table after making changes to one of my application's data tables. There was a row added when I made the changes from within Management Studio, but not when I made a similar change from within my application. So it looks as though my application doesn't cause the triggers to fire. The changes appear in the application's table in both cases.
I'm just using simple INSERT, UPDATE and DELETE statements in stored procedures in my application. No bulk inserts.
I checked the log file you mentioned and it did not show any errors during merge. The merge picks up the data that is entered through Management Studio, but does not pick up the data I enter through my application.
Everything in the replication process except for this one problem, seems to be working as expected. We created the publication in Management Studio. I then create the subscription on the client using RMO, and also run the synchronization using RMO. This is my last hurdle in getting my application to work.
Are there any special security considerations you must consider for the triggers to work. I just seems to me that the INSERT statement from my stored procedures should cause the trigger to fire, just as entering data into the table in SQL Management Studio. I'm new to using triggers, so I may not be that good at understanding what they are doing behind the scenes.
|||
I am still having the same problem with not being able to sync the data entered through my application. I even created a new publication on a new SQL 2005 Server to ensure that the problem was not with the publication/server/permissions. I reproducibly get the same error mentioned in my original post. As this is the only step left in my development and deployment of this application, I am really stuck until I can solve this problem. Does anyone from Microsoft have and suggestions as to how to troubleshoot this issue. I'll bet that the soultion will be a simple comfiguration setting, or something like that, however, I don't have enough experience to know where to look. We also have several other applications that we hope to use replication on, if we can get this problem solved.
Thanks for any help.
|||
Ok so it's pretty clear that the triggers aren't firing for some reason.
Would it be possible for you to use profiler against your database whilst running the app and any of the logs event viewer/sql logs/ merge agent logs?
It might also be worth while ensuring that the triggers are infact in place. If you script out your tables you should see some triggers that look like this attached to the tables that are participating in replication msmerge_ins_<GUID>, msmerge_upd_<GUID>, msmerge_del_<GUID>.
Cheers, James
|||I tried installing profiler on the computer which runs SQL Express 2005, but the install would not allow me to because Express is installed. The triggers are on all of the tables, and they work, as the data I enter directly into the table through Management Studio Express is correctly merged. Its just that for some reason, the triggers either don't fire, or don't operate correctly when I add data to the same tables from my application. The application is a VB.NET application and uses stored procedures which INSERT, UPDATE and DELETE data in the SQL 2005 Express database. All of the stored procedures are working as expected. I'll see if I can attach profiler to my SQL Express database from another server.|||
yes sorry I forgot you had run the inserts etc manually.
if you have the normal client tools elsewhere hopefully you'll be able to connect across using profiler. I assume there are no error messages in the logs for your issues.
Cheers, James
|||
James,
I was unable to get profiler to connect to my subscriber database.
|||
Just a thought.
Do the row GUIDs need to be named RowGUID in each table. My row GUIDs are named according to the subject of the table, but then marked as a row GUID in the column's properties. This must not make a difference, as the triggers seem to work as expected in Management Studio.
Does anyone know if the Table Adapter's in Visual Studio require any special setup to allow the triggers to fire from within the application. I use DataSets/Table Adapters to design my Queries and Data Access Layer.
|||
No they don't need to be named rowguid in order for everything to work.
What problems are you experiencing connecting profiler to your subscriber?
Sorry I don't know anything about Table adaptors so I'll have to leave that to someone else.
|||
I found the problem.
In Visual Studio you must create a connection string to connect to the databases in your application. One of the advanced features of the connection is a parameter called Replication. It has values of True and False. It was set to True, which seemed logical, since I wanted to do replication of the database. However, True means that the application is recognized as a replication agent. Replication triggers do not fire for replication agents. I set the value to false, and now everything works as expected.
Thanks for your help.
sql
