Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.
|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.
|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.sql

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

merge replication with large amounts of updates at publisher

I have merge repl setup and it has been running with no problem... until a
scheduled task that runs once every night and updates a very large amount of
records was introduced. Merge repl is scheduled to run every hour. Now the
merge attempt directly following the nightly task that updates a large
amount of udates fails, and continues to fail every hour. It eventually does
succeed sometime in the evening. The scheduled task and initial repl failure
occur around 4am eastern time. I would think that would be a somewhat low
traffic time on the net... maybe not. It usually succeeds sometime between 6
and 8 pm eastern time.
1) when a large amount of updates occur, is every record in its entirety
sent to the subscriber? or does the merge repl system automatically package
things into more effecient jobs of some sort?
2) when repl does finally succeed it reports taking over 1 hour. Since my
schedule is every hour could that be part of, or THE problem? meaning a
merge job is scheduled to start but the last one is still running? could
that be why its failing?
3) could any concurrency/locking type issues occur at the subscriber while
the merge agent is operating? especially since it is continually failing and
retrying and failing and retrying?
4) is 50,000 records just way to many to expect to be able to merge? (over
the internet)
any info is greatly appreciated. Thanks.
1) replication metadata is compared to determine which row(s) need
updating/deleting/inserting. Basically the way it works is that you have a
rowguid which identifies each row. If a row is modified triggers write the
rowguid and some tracking information to the msmerge_contents table.
The merge agent will read all rows in the msmerge_contents table have been
added since the last time it ran. It will then compare the version
(generation) of this row with the version of the row on the
publisher/subscriber. The publisher/subscriber with the latest generation is
determined to be the winner, and then the source table on the publisher or
subscriber is consulted and the data values are extracted, and a
insert/update/delete statement is fired on the subscriber/publisher.
So initially its just the rowguid and version information which travels
across the wire, and then it could be the row itself.
2) I think you would benefit by setting QueryTimeOut to something large, it
600. You can set this by right clicking on your merge agent, selecting agent
properties, clicking steps, click run agent, and then in the commands
section, click edit, and look for -QueryTimeOut and set it to 600. It its
not there add it. The merge agent will run for a predetermined time and then
may fail. Sometimes it will take 3 or 4 times before it completely processes
all transactions. Many DBA wrap their merge agents in infinite loops. The
job scheduler for SQL Server will not run two simultaneous instances of the
same merge agent.
3) Its possible. Normally you will get a deadlock message in your merge
agent or msrepl_errors. Normally you run into locking/concurrency problems
when you have a large number of merge agents running simultaneously. There
is a setting to limit the number of concurrent merge agents. Right click on
your publication, select publication properties, and then click the
subscriber tab. Many DBAs will stagger their merge agents so they don't run
simultaneously. For instance they will schedule them to run every 17 minutes
or so. There is nothing magic about 17, its just a larger prime number.
4) Not at all, but this depends on hardware of the publisher and subscriber
and the bandwidth available. You might benefit from the slow link profile if
you have low bandwidth.
"djc" <noone@.nowhere.com> wrote in message
news:OV931hVrEHA.332@.TK2MSFTNGP14.phx.gbl...
>I have merge repl setup and it has been running with no problem... until a
> scheduled task that runs once every night and updates a very large amount
> of
> records was introduced. Merge repl is scheduled to run every hour. Now the
> merge attempt directly following the nightly task that updates a large
> amount of udates fails, and continues to fail every hour. It eventually
> does
> succeed sometime in the evening. The scheduled task and initial repl
> failure
> occur around 4am eastern time. I would think that would be a somewhat low
> traffic time on the net... maybe not. It usually succeeds sometime between
> 6
> and 8 pm eastern time.
> 1) when a large amount of updates occur, is every record in its entirety
> sent to the subscriber? or does the merge repl system automatically
> package
> things into more effecient jobs of some sort?
> 2) when repl does finally succeed it reports taking over 1 hour. Since my
> schedule is every hour could that be part of, or THE problem? meaning a
> merge job is scheduled to start but the last one is still running? could
> that be why its failing?
> 3) could any concurrency/locking type issues occur at the subscriber while
> the merge agent is operating? especially since it is continually failing
> and
> retrying and failing and retrying?
> 4) is 50,000 records just way to many to expect to be able to merge? (over
> the internet)
> any info is greatly appreciated. Thanks.
>
|||Thanks Hilary.
1) You suggested adding the QueryTimeOut setting directly to the command in
the agent. I just want to verify that the dialog box returned from right
clicking on the merge agent (from the replication monitor) and choosing
Agent Profile allows you to do the same thing. The dialog has several
template profiles like the Slow Link one you mentioned and others. I have
played with the QueryTimeOut value from there. Just want to make sure thats
ok as well since you suggested adding this option to the command itself..
any difference?
2) "The merge agent will run for a predetermined time and then may fail.
Sometimes it will take 3 or 4 times before it completely processes all
transactions. Many DBA wrap their merge agents in infinite loops. The job
scheduler for SQL Server will not run two simultaneous instances of the same
merge agent."
Well, wrapping the agents in an infinite loop is beyond my current skill
level although if it comes to that then I will have to learn. When you said
that the agent will run for a predetermined time and then fail. Sometimes 3
or 4 times before it completely processes all transactions... after failing
does it pick up where it left off? or does it start all over again? If it
picks up where it left off does that mean that its possible that out of a
50,000 record merge job that takes 6 times to complete that the records
updated during each of the failed attempts are committed before the final
completion of the whole 50,000 job?
3) In Books Online I read about the MaxUploadChanges and MaxDownloadChanges.
It said you can set a limit on the number of records updated per merge agent
session. But it didn't say what happens if you hit that limit. Lets say you
set a limit to 2500 records but you have 30,000 records that need to be
merged. After is stops at 2500 will the remaining changes still be processed
2500 at a time during subsequesnt merge agent runs? If so this may allow me
to split up my 1 time large update into smaller chunks since it only happens
once over night?
thanks again for all your help. It is very much appreciated.
-djc
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Ov2WLwXrEHA.1204@.TK2MSFTNGP12.phx.gbl...
> 1) replication metadata is compared to determine which row(s) need
> updating/deleting/inserting. Basically the way it works is that you have
a
> rowguid which identifies each row. If a row is modified triggers write the
> rowguid and some tracking information to the msmerge_contents table.
> The merge agent will read all rows in the msmerge_contents table have been
> added since the last time it ran. It will then compare the version
> (generation) of this row with the version of the row on the
> publisher/subscriber. The publisher/subscriber with the latest generation
is
> determined to be the winner, and then the source table on the publisher or
> subscriber is consulted and the data values are extracted, and a
> insert/update/delete statement is fired on the subscriber/publisher.
> So initially its just the rowguid and version information which travels
> across the wire, and then it could be the row itself.
> 2) I think you would benefit by setting QueryTimeOut to something large,
it
> 600. You can set this by right clicking on your merge agent, selecting
agent
> properties, clicking steps, click run agent, and then in the commands
> section, click edit, and look for -QueryTimeOut and set it to 600. It its
> not there add it. The merge agent will run for a predetermined time and
then
> may fail. Sometimes it will take 3 or 4 times before it completely
processes
> all transactions. Many DBA wrap their merge agents in infinite loops. The
> job scheduler for SQL Server will not run two simultaneous instances of
the
> same merge agent.
> 3) Its possible. Normally you will get a deadlock message in your merge
> agent or msrepl_errors. Normally you run into locking/concurrency problems
> when you have a large number of merge agents running simultaneously. There
> is a setting to limit the number of concurrent merge agents. Right click
on
> your publication, select publication properties, and then click the
> subscriber tab. Many DBAs will stagger their merge agents so they don't
run
> simultaneously. For instance they will schedule them to run every 17
minutes
> or so. There is nothing magic about 17, its just a larger prime number.
> 4) Not at all, but this depends on hardware of the publisher and
subscriber
> and the bandwidth available. You might benefit from the slow link profile
if[vbcol=seagreen]
> you have low bandwidth.
>
> "djc" <noone@.nowhere.com> wrote in message
> news:OV931hVrEHA.332@.TK2MSFTNGP14.phx.gbl...
a[vbcol=seagreen]
amount[vbcol=seagreen]
the[vbcol=seagreen]
low[vbcol=seagreen]
between[vbcol=seagreen]
my[vbcol=seagreen]
while[vbcol=seagreen]
(over
>
sql

Merge Replication Updates on Initialise (SQLServer 2005)

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?

I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

Hi Steve,

When you initialize the client, you are probably using dynamic snapshot, and when those dynamic snapshots become old the initializing will also need to get the incremental changes which happened after the old dynamic snapshot generated.

You can choose to have the snapshots refreshed on a schedule so new Subscribers that subscribe to a partition for which a snapshot has been created will receive an up-to-date snapshot.

For more information, please refer to Books online topic: Snapshots for Merge Publications with Parameterized Filters

Hope it helps.

Wanwen

|||I assume you mean the snapshot agent running regularly - which it does every night.

Seems to be something else - I'll just have to keep looking|||

If you are using dynamic filtering, there are 2 snapshots to consider:

1. Regular snapshots

2. Dynamic snapshots

The regular snapshot do not have data in them.

The dynamic snapshots are the ones that have data in them.

You need to make sure that both are run before your subscriber syncs so that they can use the bcp files from the dynamic snapshot to complete the sync process in a much more timely manner (faster).

sql

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production

environment. The architecture is filtered merge replication between two SQL

Server 2000 SP4 databases. The publisher is standard edition and the subscriber

is personal edition.

The issue is that is I update certain rows on the subscriber the data is not

replicated to the publisher. Inserts and deletes seem to replicate correctly.

The issue is specific to certain rows in some tables. If I update other rows

the updates replicate correctly. The subscription has been re-initialized once

and it did not fix the issue. The replication process indicates that there was

not data to merge and the subscriber updates remain and are at that point

different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and

checking the publisher database. The synchronization process is executed using

the merge agent ActiveX control.

This issue started occurring after a large conflict was generated during an

earlier synch on this particular subscriber. We have other subscribing

databases that are working fine.

I have already spent 8+ hours on the phone with TCS trying to solve this issue.

If anyone has any ideas or has seen this type of issue before I would appreciate

any direction to try to fix this issue.

Thanks

I ran another test today. I created a new database on a separate

server and added a subscription to the publication and synced. The data came

down correctly but my local updates to the specific rows where still ignored

during the sync process.

I then updated the problem row in the publisher, synced to the new subscriber

and now I can update the local row and it will sync back up to the publisher.

Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on

the subscriber will not replicate to the publisher. These rows where all

involved in a conflict at one time that was resolved. I setup a new database,

subscriptions and synced and updates to these 5 rows will not propagate to the

server.

I am updating a GUID field that is not the primary key and has no relation to

the partition which is based on a join filter between these tables. The field I

am updating is a last update key and its only purpose it to have a unique GUID

that changes with each update. I can change other fields and the changes do not

go to the publisher as well. The issue is definitely related to these rows as

other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the

rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.|||

I have a question related to this issue:

I executed the sp_showreplicainfo stored procedure on the rows that will not

update on the subscriber. The row info returned two rows. One with the server

name set to the publisher database and another with the server name set to one

of the subscribers (not the one I am testing with).

Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.|||

Something interesting came up related to this issue while

working with Microsoft tech support. I have twotables in my database that

have a one-to-one relationship with each other. In the two tables the rowid is the primary

key. This means that I have duplicate rowid values in different tables (The

same rowid value is in 2 tables at the same time).

Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count,*FROM sysmergearticles WHERE name LIKE'%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production

environment. The architecture is filtered merge replication between two SQL

Server 2000 SP4 databases. The publisher is standard edition and the subscriber

is personal edition.

The issue is that is I update certain rows on the subscriber the data is not

replicated to the publisher. Inserts and deletes seem to replicate correctly.

The issue is specific to certain rows in some tables. If I update other rows

the updates replicate correctly. The subscription has been re-initialized once

and it did not fix the issue. The replication process indicates that there was

not data to merge and the subscriber updates remain and are at that point

different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and

checking the publisher database. The synchronization process is executed using

the merge agent ActiveX control.

This issue started occurring after a large conflict was generated during an

earlier synch on this particular subscriber. We have other subscribing

databases that are working fine.

I have already spent 8+ hours on the phone with TCS trying to solve this issue.

If anyone has any ideas or has seen this type of issue before I would appreciate

any direction to try to fix this issue.

Thanks

I ran another test today. I created a new database on a separate

server and added a subscription to the publication and synced. The data came

down correctly but my local updates to the specific rows where still ignored

during the sync process.

I then updated the problem row in the publisher, synced to the new subscriber

and now I can update the local row and it will sync back up to the publisher.

Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on

the subscriber will not replicate to the publisher. These rows where all

involved in a conflict at one time that was resolved. I setup a new database,

subscriptions and synced and updates to these 5 rows will not propagate to the

server.

I am updating a GUID field that is not the primary key and has no relation to

the partition which is based on a join filter between these tables. The field I

am updating is a last update key and its only purpose it to have a unique GUID

that changes with each update. I can change other fields and the changes do not

go to the publisher as well. The issue is definitely related to these rows as

other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the

rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.|||

I have a question related to this issue:

I executed the sp_showreplicainfo stored procedure on the rows that will not

update on the subscriber. The row info returned two rows. One with the server

name set to the publisher database and another with the server name set to one

of the subscribers (not the one I am testing with).

Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.|||

Something interesting came up related to this issue while

working with Microsoft tech support. I have twotables in my database that

have a one-to-one relationship with each other. In the two tables the rowid is the primary

key. This means that I have duplicate rowid values in different tables (The

same rowid value is in 2 tables at the same time).

Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.