Merge rplication - records not being replicated.

We are replicating 5 subscriptions to 500 users with MSDE databases. All
databases are SQL Server 2000 SP4.
The issue we are seeing is that some users are not seeing all the records
they should. (Merge replication) Examples:
User A creates some new records. The records get replicated to the server
and then are not in user A's database (they should still be in the users
database). There are no conflicts. Re-building the snap shot and
re-initializing with the upload changes selected does not fix the issue.
Re-building the snap shot and re-initializing with the upload changes Not
selected does fix the issue.
Any suggestions for tracking this proble down would be appreciated.
Are you using any dynamic filters? If the added rows on A are not allowed
according to the filter, the row is outside of the partition and this could
account for such behaviour.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
We are using dynamic filters. The users are only allowed to add records
that are in the partition.
|||Then this is normal behaviour.
It would help if you explain a little more about what you would like to
happen.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication Topology

Hi everyone,
Is it possible to have three publishers who also subscribe to
publications from each other? All are pull subscriptions participating
in merge replication. The physical design is represented by a triangle.
My understanding of the suppported replication topology precludes such
a design - is this the case?
Paul
Hey Paul,
Triangle, or Peer To Peer Topologies are not supported in SQL2000.
You can 'fake' it with some heavy duty partitioning if you need to.
Take a peek at SQL2005 for that kind of functionality out of the box.
Donna Lambert
"Waterfall" wrote:

> Hi everyone,
> Is it possible to have three publishers who also subscribe to
> publications from each other? All are pull subscriptions participating
> in merge replication. The physical design is represented by a triangle.
> My understanding of the suppported replication topology precludes such
> a design - is this the case?
> Paul
>
|||Thanks Donna. It is good to know that SQL2005 will support peer-to-peer
replication topology.
I have another question. Is it possible to replicate to a table
containing an identity column and a natural key using merge or
transactional replication, where the identity values are not preserved?
Paul
*** Sent via Developersdex http://www.codecomments.com ***

Merge replication throwing very weird error messages

I've been setting up subscriptions to a merge publication for the past 3 days. All of a sudden, I'm getting a pile of very strange errors. Replication is configured. I have 16 subscribers to an existing publication configured and synchronizing changes without any issues. The script that I'm using to create all of the subscriptions is as follows:

use [PIC]

exec sp_addmergesubscription @.publication = N'PIC', @.subscriber = N'machinename\SQLEXPRESS',

@.subscriber_db = N'MyDatabase', @.subscription_type = N'Push', @.sync_type = N'Automatic',

@.subscriber_type = N'Global', @.subscription_priority = 75, @.description = N'', @.use_interactive_resolver = N'False'

exec sp_addmergepushsubscription_agent @.publication = N'PIC', @.subscriber = N'machinename\SQLEXPRESS',

@.subscriber_db = N'MyDatabase', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1,

@.publisher_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0,

@.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0,

@.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0

GO

The last one that I added #17, gives the following errors after successfully creating the subscription.

Command attempted:

{call sys.sp_MSmergesubscribedb ('true', 0) }

Error messages:

The merge process could not initialize the subscription. Ensure that the subscription registration exists at the publisher, and reregister the subscription if necessary. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201011)
Get help: http://help/MSSQL_REPL-2147201011

RegCreateKeyEx() returned error 5, 'Access is denied.' (Source: MSSQLServer, Error number: 22002)
Get help: http://help/22002

Could not add article resolver 'Microsoft SQL Server Additive Conflict Resolver' information to the registry (Source: MSSQLServer, Error number: 21713)
Get help: http://help/21713

Could not register article resolver: 'Microsoft SQL Server Additive Conflict Resolver'. (Source: MSSQLServer, Error number: 21715)
Get help: http://help/21715

The system tables for merge replication could not be created successfully. (Source: MSSQLServer, Error number: 20008)
Get help: http://help/20008

I've tried to manually create it using the GUI and get an even stranger error message as follows:

TITLE: New Subscription Wizard

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476

ADDITIONAL INFORMATION:

Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476

BUTTONS:

OK

I very obviously have replication installed by virtue of the fact that I have a publication and 16 subscriptions that are currently synchronizing. Any ideas?

Yes, I can connect to the subscriber from the server. That's how I created the subscription database in the first place, by connecting a query window to the subscriber and issuing a create database statement.|||

Is it the same account you are using to run this command as the other 16 subscriptions?

Did the sql server service account change between the success and this failure attempt?

Did you add sp_addmergesubscription_agent to those successful subscriptions too?

|||Same account was used to run these two commands for all of the other 16 subscriptions. No, the service account did not change. Yes, we successfully added sp_addmergesubscription_agent to the other 16.

Merge replication throwing very weird error messages

I've been setting up subscriptions to a merge publication for the past 3 days. All of a sudden, I'm getting a pile of very strange errors. Replication is configured. I have 16 subscribers to an existing publication configured and synchronizing changes without any issues. The script that I'm using to create all of the subscriptions is as follows:

use [PIC]

exec sp_addmergesubscription @.publication = N'PIC', @.subscriber = N'machinename\SQLEXPRESS',

@.subscriber_db = N'MyDatabase', @.subscription_type = N'Push', @.sync_type = N'Automatic',

@.subscriber_type = N'Global', @.subscription_priority = 75, @.description = N'', @.use_interactive_resolver = N'False'

exec sp_addmergepushsubscription_agent @.publication = N'PIC', @.subscriber = N'machinename\SQLEXPRESS',

@.subscriber_db = N'MyDatabase', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1,

@.publisher_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0,

@.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0,

@.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0

GO

The last one that I added #17, gives the following errors after successfully creating the subscription.

Command attempted:

{call sys.sp_MSmergesubscribedb ('true', 0) }

Error messages:

The merge process could not initialize the subscription. Ensure that the subscription registration exists at the publisher, and reregister the subscription if necessary. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201011)
Get help: http://help/MSSQL_REPL-2147201011

RegCreateKeyEx() returned error 5, 'Access is denied.' (Source: MSSQLServer, Error number: 22002)
Get help: http://help/22002

Could not add article resolver 'Microsoft SQL Server Additive Conflict Resolver' information to the registry (Source: MSSQLServer, Error number: 21713)
Get help: http://help/21713

Could not register article resolver: 'Microsoft SQL Server Additive Conflict Resolver'. (Source: MSSQLServer, Error number: 21715)
Get help: http://help/21715

The system tables for merge replication could not be created successfully. (Source: MSSQLServer, Error number: 20008)
Get help: http://help/20008

I've tried to manually create it using the GUI and get an even stranger error message as follows:

TITLE: New Subscription Wizard

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476

ADDITIONAL INFORMATION:

Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476

BUTTONS:

OK

I very obviously have replication installed by virtue of the fact that I have a publication and 16 subscriptions that are currently synchronizing. Any ideas?

Yes, I can connect to the subscriber from the server. That's how I created the subscription database in the first place, by connecting a query window to the subscriber and issuing a create database statement.|||

Is it the same account you are using to run this command as the other 16 subscriptions?

Did the sql server service account change between the success and this failure attempt?

Did you add sp_addmergesubscription_agent to those successful subscriptions too?

|||Same account was used to run these two commands for all of the other 16 subscriptions. No, the service account did not change. Yes, we successfully added sp_addmergesubscription_agent to the other 16.

Merge Replication Status

I'm using sp_replmonitorhelpmergesession to determine the status of
multiple push subscriptions.
When I set the Merge Agent to Slow Link, I get a status of 4 and the
message "No Data needed to be Merged" when there are no changes.
I'd like to get it to return the Status 4 when it's idle, but I want
to increase the polling interval from 60 to 180.
So I copy the Agent Profile "Slow Link" and alter the polling interval
and validation intervals only.
When I copy the slow Link Profile and use the copy, I get a Status of
3 and the message "Waiting xxx second(s) before polling for further
changes."
Where/how are these controlled?
pollinginterval
Hilary Cotter
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
"Brian Bunin" <BBunin@.SBCGlobal.net> wrote in message
news:1176335136.270222.63650@.n59g2000hsh.googlegro ups.com...
> I'm using sp_replmonitorhelpmergesession to determine the status of
> multiple push subscriptions.
>
> When I set the Merge Agent to Slow Link, I get a status of 4 and the
> message "No Data needed to be Merged" when there are no changes.
> I'd like to get it to return the Status 4 when it's idle, but I want
> to increase the polling interval from 60 to 180.
> So I copy the Agent Profile "Slow Link" and alter the polling interval
> and validation intervals only.
> When I copy the slow Link Profile and use the copy, I get a Status of
> 3 and the message "Waiting xxx second(s) before polling for further
> changes."
> Where/how are these controlled?
>
|||On Apr 12, 8:48 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> pollinginterval
> --
> Hilary Cotter
>
Yes, but why does changing that alter the behavior of the Status and
messages?

Merge Replication Snapshot Generation - Need Best Practice

SQL Server 2005 & Express
All,
What is a best practice for generating snapshots in my merge
replication topology?
In my topology subscriptions will be coming and going over time.
There will be @.150 users with new employees coming online over time.
Some of the users share hardware and so they may have a subscription
on one machine for several months and then stop using the system for a
few months, and then start a new subscription on a different machine.
In such a scenario would it be better for me to expire subscriptions
on a regular basis?
I would evaluate using anonymous subscribers with dynamic filtering based on
user_name.
I think you will find a smaller retention period will mean shorted sync
times especially if your users are off line for appreciable amounts of time.
Hilary Cotter
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
"Crash" <sourcenexus@.san.rr.com> wrote in message
news:1174326055.221629.90730@.n76g2000hsh.googlegro ups.com...
> SQL Server 2005 & Express
> All,
> What is a best practice for generating snapshots in my merge
> replication topology?
> In my topology subscriptions will be coming and going over time.
> There will be @.150 users with new employees coming online over time.
> Some of the users share hardware and so they may have a subscription
> on one machine for several months and then stop using the system for a
> few months, and then start a new subscription on a different machine.
> In such a scenario would it be better for me to expire subscriptions
> on a regular basis?
>
sql

Merge Replication Push using IP address fails? WTF?

I have a Merge publication that has multiple subscribers. They are all SQL Express, so it's all Push Subscriptions.

When I try to add a subscriber by it's IP address, the snapshot gets delivered. Schema created, data loaded, but I see the following error in Replication Monitor:

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

Cannot find the object 'MSmerge_ctsv_E0801EECCA824BB29D48D36D64D2BAEA', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151)

When I change the IP address in the script to the name of the machine it works perfectly. The ONLY thing I changed in the script was the IP address to the name.

The reason for this is that I have several client machines that will have connectivity, but NO name resolution back at the Server.

Please, Please, Please help!

Sample follows:

Code Snippet

execsp_addmergesubscription

@.publication = N'TestPub',

@.subscriber = N'10.126.22.30',

@.subscriber_db = N'dbTest',

@.subscription_type = N'Push',

@.sync_type = N'Automatic',

@.subscriber_type = N'Local',

@.subscription_priority = 0,

@.description =null,

@.use_interactive_resolver = N'False'

execsp_addmergepushsubscription_agent

@.publication = N'TestPub',

@.subscriber = N'10.126.22.30',

@.subscriber_db = N'dbTest',

@.job_login =null, @.job_password =null,

@.subscriber_security_mode = 0,

@.subscriber_login = N'Valid SQL LOGIN',

@.subscriber_password = N'ValidPassword',

@.publisher_security_mode = 1,

@.frequency_type = 64,

@.frequency_interval = 0,

@.frequency_relative_interval = 0,

@.frequency_recurrence_factor = 0,

@.frequency_subday = 0,

@.frequency_subday_interval = 0,

@.active_start_time_of_day = 0,

@.active_end_time_of_day = 235959,

@.active_start_date = 20070607,

@.active_end_date = 99991231,

@.enabled_for_syncmgr = N'False'

Pushing to an IP address is not supported. No doubt that is why it is failing. You must push to the server name. However you can setup an Alias and push to that. I think the Alias must match the server name.

I did read somewhere that Aliases may not be supported either but I know that does work. You could also consider adding entries to your hosts file.

Martin

|||

DOH!

Thanks for the reply.

I really HATE having to edit the hosts file......

Merge replication Primary Key violations

I have a Merge Replication Problem and I'm not sure how to proceed.

Given the following:

Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue

The replication is configured to resolve to the greater integer value.

Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)

One of the inserts will get to the Central Server first.
The other will form a conflict.

A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.

So now I have two different value fields and a conflict that cannot be resolved.

Is this fixable?
Do I need to make changes at athe Architechture level?

Hi Brian,

if you need to store both records from all subscribers, You should add a column for client id .So once tables replicates from all subscribers each client has their own id.

Example: if client 1 has the code 100 and client 2 has the code 200

the table at subscriber will be : 100,'Fred',1 (Record comes from client 1)

200,'Fred',1 (Record comes from client 2)

if you need to resolve the conflict and to store 1 record only you should assign priority for each subscriber

For more details check the following URL : http://support.microsoft.com/kb/315514

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I need to store only the higher of the two.

If the record already exists and both try to update the record, only the higher is stored, as desired.

But when the record does not exist, both subscribers do an insert, and it does not store only the higher.

Reading all the KB articles on merge replication, it appears that Updates and Inserts are supposed to be resolved the same.

I can't use subscriber priority, as I don't care WHERE the record came from, only the value stored.

Merge replication new subscriber problem

Hi all,
i can't create new subscriptions for my publication since i made a
change to a view:
i used to have a view called vlAnag
i changed the view adding a reference (join) to a new view called
vlAnagValidi
then added the new view to the articles in my publication
since that moment when i try to synchronize i get the error
'the schema script ...\vlAnag_999.sch could not be propagated to the
subscriber'
it seems that he tries to create the view vlAnag *before* creating the
vlAnagValidi
in fact i can't find it in the new database
how can i change the order in which the merge process initializes the
schema?
or is there any workaround?
thanx in advance
lorenzo
The workaround on sql 2000 is to recreate the views and then sql server
should create the correct dependencied and therefore establish the required
replication order. Alternatively you could use sp_addscriptexec. In SQL
Server 2005 you can specify the article order and dependencies are
apparently more robust.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i can't delete the views:
error 3724 cannot drop the view ... because it is being used for
replication
of course
now i'm going to study the sp_addscriptexec...
see you
lorenzo
|||I would recommend removing all the views and any other programming objects
from your merge publication if you have to reinitialize at some point. In my
case they are part of a snapshot publication only, which gives me the
flexibility that I need. The sp_addscriptexec can be used but you have to
hand script the file. In your case this is probably ideal as you can
manually determine the replicaiton order of articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanx for your reply
i have to manage a 150 articles publication, therefore i'm afraid i
won't edit a manual script...
the (ugly but working) solution i'm taking is to launch the sync, wait
for it to stop for the error, launch a little script that creates the
view, and finally restart the sync
(no comments, please)
my subscribers are very few, so i can handle it :-)
ciao
lorenzo