Merge Replication Issue. Update not happening

Hi,
We have set up merge replication between a few boxes. I make changes on one
box, replicate, and check the tables on the other box. The inserts are being
replicated, and some updates.
It appears that the updates made out of a stored proc are not being
replicated. I can manually update the table, and it will be replicated. But
when updated from the stored proc, it is not coming accrossed.
Any ideas?
Steve
More info.
sp3, no conflicts reported, after further testing, it seems one column on
one table never gets updated. Other columns that didn't, did at other times.
It is of type bit. I can manually update it and replicate, but can't have
the stored procedure update it and replicate.
"SteveInBeloit" wrote:

> Hi,
> We have set up merge replication between a few boxes. I make changes on one
> box, replicate, and check the tables on the other box. The inserts are being
> replicated, and some updates.
> It appears that the updates made out of a stored proc are not being
> replicated. I can manually update the table, and it will be replicated. But
> when updated from the stored proc, it is not coming accrossed.
> Any ideas?
> Steve
>
|||Just went to sp4, that did not help. When I do a "validate" after the
replicate, the checksum for that table fails.
"SteveInBeloit" wrote:
[vbcol=seagreen]
> More info.
> sp3, no conflicts reported, after further testing, it seems one column on
> one table never gets updated. Other columns that didn't, did at other times.
> It is of type bit. I can manually update it and replicate, but can't have
> the stored procedure update it and replicate.
>
> "SteveInBeloit" wrote:
|||More info - it is a Pull initiated at the subscriber. If I run the same
application on the Publisher, and make the updates, then replicate, the
change gets to the subscriber, just not the other way around.
"SteveInBeloit" wrote:
[vbcol=seagreen]
> Just went to sp4, that did not help. When I do a "validate" after the
> replicate, the checksum for that table fails.
> "SteveInBeloit" wrote:
|||Solved:
It was actually a delete and an insert, not an update. A record had been
written to a ##TempTable. Processing was done on that record. Then the GUID
key of that row was used to delete the row out of the real table, then and
INSERT from the temp to the real table to put it back. On that insert, I
used the same GUID key instead of letting the table NEWID get a new one. It
confused replication, having known there was a DELETE and and INSERT, but the
GUIDs the same.
"SteveInBeloit" wrote:
[vbcol=seagreen]
> More info - it is a Pull initiated at the subscriber. If I run the same
> application on the Publisher, and make the updates, then replicate, the
> change gets to the subscriber, just not the other way around.
> "SteveInBeloit" wrote:

Merge replication deleting inserts on subscriber duringsynchronisation

I am having a problem with merge replication in that inserts at the
subscriber are being deleted after synchronising. In a test the
results show up as 1 insert, 1 delete, 0 conflicts (for a single
record created). After synchronising the new record is correctly
inserted on the server but is deleted on the subscriber - this delete
should not be occuring according to the filters set. The replication
was working OK before but I have made some significant changes.
The merge replication is using web synchronisation with RMO in the
application. The publication has several articles, some of them
download only, with row parameterisation using HOST_NAME and
overlapping partitions. There is a table of clients including a field
for the host names (nvarchar(32)) as the basis for the filtering.
The system is currently on a development server running SQL Server
2005 Workgroup edition with the clients running SQL Server 2005
Express. I have been developing the deployment of the application
which has involved some changes, most significantly (I think):
- the subscriber database is now initially created by scripts (i.e.
create database, create table, etc) rather than attaching a previously
created database
- the initial snapshot is initiated by the subscriber
- the data on the subscriber is populated from the download tables on
first synchronisation
I suspect it is something to do with partitioning and the filtering
but I am not sure what is wrong and how to rectify the situation. (If
I set the @.partition_options to 2 or 3 it produces a conflict error
saying that out of partition changes are not allowed - for that
article the data is non-overlapping).
Some additional notes and some of the key settings:
PUBLICATION SETTINGS:
@.allow_pull = N'true'
@.allow_anonymous = N'true'
@.enabled_for_internet = N'false'
@.allow_subscription_copy = N'false'
@.dynamic_filters = N'true'
@.validate_subscriber_info = N'HOST_NAME()'
@.use_partition_groups = N'true'
@.allow_subscriber_initiated_snapshot = N'true'
@.allow_web_synchronization = N'true'
@.allow_partition_realignment = N'true'
EXAMPLE ARTICLE SETTINGS:
@.subscriber_upload_options = 0 (no restrictions)
@.partition_options = 0 (overlapping; 2 and 3 produce conflict errors)
@.check_permissions = 0 (permissions not checked)
@.subset_filterclause = N'[Computer] = (Select ComputerID From
Computers Where ComputerName = HOST_NAME())'
@.column_tracking = N'false'
@.vertical_partition = N'false'
(other articles have similar (though more complex) filter clauses or
have a join based on the one above)
SUBSCRIPTION SETTINGS:
@.subscriber_type = N'anonymous'
@.subscription_priority = 0
@.sync_type = N'Automatic'
SUBSCRIPTION AGENT SETTINGS:
@.distributor_security_mode (set in RMO to standard/SQL)
@.publisher_security_mode (set in RMO to standard/SQL)
@.internet_security_mode = 0 (SQL)
Subscriptions initiated by subscribers do not appear in list under
"Data Partitions" in the Publication Properties on the server whereas
they seemed to before when the subscription was added through SSMS on
the server.
There is no specific snapshot folder generated for the subscriber as
seemed to be the case when the subscription was added through SSMS on
the server.
In the Replication Monitor the subscriber-initiated subscription
appears several times; possibly due to uninstalling and re-installing
(and hence re-initialising) the same subscriber several times during
development.
The client subscription appears (just once) in the
sysmergesubscriptions and MSmerge_subscriptions tables on the server.
Any help on this greatly appreciated. I am relatively new to
replication so please let me know if there are any key settings I have
ommitted from this post and please forgive me if I have missed
something stupid.
can you script out the problem table(s) and post your publication and
subscription scripts here?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JDS" <admin@.jeremysage.com> wrote in message
news:ceaf1828-88f3-4418-aaab-2fe6a3dbd576@.s12g2000prg.googlegroups.com...
>I am having a problem with merge replication in that inserts at the
> subscriber are being deleted after synchronising. In a test the
> results show up as 1 insert, 1 delete, 0 conflicts (for a single
> record created). After synchronising the new record is correctly
> inserted on the server but is deleted on the subscriber - this delete
> should not be occuring according to the filters set. The replication
> was working OK before but I have made some significant changes.
> The merge replication is using web synchronisation with RMO in the
> application. The publication has several articles, some of them
> download only, with row parameterisation using HOST_NAME and
> overlapping partitions. There is a table of clients including a field
> for the host names (nvarchar(32)) as the basis for the filtering.
> The system is currently on a development server running SQL Server
> 2005 Workgroup edition with the clients running SQL Server 2005
> Express. I have been developing the deployment of the application
> which has involved some changes, most significantly (I think):
> - the subscriber database is now initially created by scripts (i.e.
> create database, create table, etc) rather than attaching a previously
> created database
> - the initial snapshot is initiated by the subscriber
> - the data on the subscriber is populated from the download tables on
> first synchronisation
> I suspect it is something to do with partitioning and the filtering
> but I am not sure what is wrong and how to rectify the situation. (If
> I set the @.partition_options to 2 or 3 it produces a conflict error
> saying that out of partition changes are not allowed - for that
> article the data is non-overlapping).
> --
> Some additional notes and some of the key settings:
> PUBLICATION SETTINGS:
> @.allow_pull = N'true'
> @.allow_anonymous = N'true'
> @.enabled_for_internet = N'false'
> @.allow_subscription_copy = N'false'
> @.dynamic_filters = N'true'
> @.validate_subscriber_info = N'HOST_NAME()'
> @.use_partition_groups = N'true'
> @.allow_subscriber_initiated_snapshot = N'true'
> @.allow_web_synchronization = N'true'
> @.allow_partition_realignment = N'true'
> EXAMPLE ARTICLE SETTINGS:
> @.subscriber_upload_options = 0 (no restrictions)
> @.partition_options = 0 (overlapping; 2 and 3 produce conflict errors)
> @.check_permissions = 0 (permissions not checked)
> @.subset_filterclause = N'[Computer] = (Select ComputerID From
> Computers Where ComputerName = HOST_NAME())'
> @.column_tracking = N'false'
> @.vertical_partition = N'false'
> (other articles have similar (though more complex) filter clauses or
> have a join based on the one above)
> SUBSCRIPTION SETTINGS:
> @.subscriber_type = N'anonymous'
> @.subscription_priority = 0
> @.sync_type = N'Automatic'
> SUBSCRIPTION AGENT SETTINGS:
> @.distributor_security_mode (set in RMO to standard/SQL)
> @.publisher_security_mode (set in RMO to standard/SQL)
> @.internet_security_mode = 0 (SQL)
> Subscriptions initiated by subscribers do not appear in list under
> "Data Partitions" in the Publication Properties on the server whereas
> they seemed to before when the subscription was added through SSMS on
> the server.
> There is no specific snapshot folder generated for the subscriber as
> seemed to be the case when the subscription was added through SSMS on
> the server.
> In the Replication Monitor the subscriber-initiated subscription
> appears several times; possibly due to uninstalling and re-installing
> (and hence re-initialising) the same subscriber several times during
> development.
> The client subscription appears (just once) in the
> sysmergesubscriptions and MSmerge_subscriptions tables on the server.
> --
> Any help on this greatly appreciated. I am relatively new to
> replication so please let me know if there are any key settings I have
> ommitted from this post and please forgive me if I have missed
> something stupid.
|||Update:
I have managed to resolve this, so for the benefit of anyone else that
comes across the same problem, the details as follows.
The main problem was not specifying the @.hostname parameter in the
sp_addmergepullsubscription_agent stored procedure when setting up the
client. Unfortunately, it does not seem easy to set the parameter to
the host name dynamically. This is the script I ended up using
(sensitive values set to *s):
-- Parameters for sp_executesql
DECLARE @.SQLCommand nvarchar(1000)
DECLARE @.ParameterList nvarchar(1000)
-- Parameters for sp_addmergepullsubscription_agent
DECLARE @.Publisher_Name sysname
DECLARE @.Publisher_db_Name sysname
DECLARE @.Publication_Name sysname
DECLARE @.SyncMgr_Value nvarchar(5)
DECLARE @.UseResolver_Value nvarchar(5)
DECLARE @.URL_Name nvarchar(260)
DECLARE @.Login_Name sysname
DECLARE @.Password_Name nvarchar(524)
DECLARE @.Client_Name sysname
SET @.Publisher_Name = N'DILBERT\SQLSERVER2005'
SET @.Publisher_db_Name = N'WeightData'
SET @.Publication_Name = N'Synchronisation'
SET @.SyncMgr_Value = N'True'
SET @.UseResolver_Value = N'False'
SET @.URL_Name = N'https://server.*******.com/SQLReplication/
replisapi.dll'
SET @.Login_Name = N'*******'
SET @.Password_Name = '*******'
SET @.Client_Name = HOST_NAME()
-- Other settings specified in RMO code
SET @.SQLCommand = N'EXEC sp_addmergepullsubscription_agent @.publisher
= @.Publisher_Name, @.publisher_db = @.Publisher_db_Name, @.publication =
@.Publication_Name, @.enabled_for_syncmgr = @.SyncMgr_Value,
@.frequency_type = 2, @.use_interactive_resolver = @.UseResolver_Value,
@.use_web_sync = 1, @.internet_url = @.URL_Name, @.internet_login =
@.Login_Name, @.internet_password = @.Password_Name,
@.internet_security_mode = 0, @.internet_timeout = 300, @.hostname =
@.Client_Name'
SET @.ParameterList = N'@.Publisher_Name sysname, @.Publisher_db_Name
sysname, @.Publication_Name sysname, @.SyncMgr_Value nvarchar(5),
@.UseResolver_Value nvarchar(5), @.URL_Name nvarchar(260), @.Login_Name
sysname, @.Password_Name nvarchar(524), @.Client_Name sysname'
EXEC sp_executesql @.SQLCommand, @.ParameterList, @.Publisher_Name =
@.Publisher_Name, @.Publisher_db_Name = @.Publisher_db_Name,
@.Publication_Name = @.Publication_Name, @.SyncMgr_Value =
@.SyncMgr_Value, @.UseResolver_Value = @.UseResolver_Value, @.URL_Name =
@.URL_Name, @.Login_Name = @.Login_Name, @.Password_Name = @.Password_Name,
@.Client_Name = @.Client_Name
This seems cumbersome (but works). If anyone can show a simpler method
then please let us know.

Merge Replication and publisher deleting subscriber inserts.

The row was inserted at 'Subscriber Laptop' but could not be inserted at
'Publisher server'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK1'. The conflict occurred in database 'Tst', table 'Report',
column 'ReportNo'.
http://support.microsoft.com/default...b;en-us;307356
We are using Merge Replication. Sql Server 2000 SP4 on the server and MSDE
SP4 on the laptops.
We've been testing for a few months and just starting to trickle into
production. This is the first time we've seen this error. From what I could
google, the KB I refer to above says we should have NOT FOR REPLICATION on
all FK constraints.
Now, all of my hits on this subject seem to be 2003 and earlier. So before I
go ahead and make this huge change, I want to confirm that yes, infact, I
should be doing this.
Should NFR be put on Primary Keys as well?
Can this NFR be specified through VISIO as that is where we maintain the
data model. If not, is there an alter statement we can apply to constraints
to turn this option on?
Buzz.
NFR can't be put onto PKs but definitely you should have it on the FKs (see
http://www.replicationanswers.com/Me...derArticle.asp)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)