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.