Unable to replicate existing tables in Sql 2005

Question

I have a database (was previously sql 7 but all 7 replication removed and now on 2005 in 2005 compatability mode)

I am attempting to set up replication using Sql Server 2005 between two servers in different geographical locations.

The database has been restored onto both server.

I create a merge publication with no problems.

I then attempt to create a push subscription to the remote server. The subscription creates but the whenever it tries to syncronise I receive the error: "The schema script 'listBusinessUnits_2.sch' could not be propagated to the subscriber." 

I have found that I can succesfully replicate a brand new table by creating it on the publisher and allowing replication to create the table on the subscriber end.

I have also found that I can replicate any of my existing tables to a new blank database on the subscribing machine.

It just doesn't allow me to replicate when the table already exists in the database on the subscribing machine.

I have checked and the schema is the same on both databases. Both are in 2005 compatibility mode. Both are using newsequentialid() for the default value of the rowguid field.

Does anybody have any insight into this that may assist me?

Answer 1

This is likely a permissions problem. It appears your merge agent is unable to read the snapshto share. Is your sql server agent running as a local system account. It might need rights to access the desktop.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

The Merge Replication u did setup is Bi Directional or  not.

Also check if u have appropriate permission to the Subscribing Server and database.

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 3

Thanks guys.

My merge replication is set up to be bi-directional.
I am running the snapshot agent as a domain user account.
I arranged for that user to have full permissions to the directory
    'c:\program files\microsoft sql server\mssql.1\mssql\repldata\unc\

which is where it appeared to be wanting to create the snapshots. The user has permission to this folder on both the publishing computer and the subscribing computer. You mentioned the user might need permissions to read the desktop. By that do you mean the windows desktop?

I did find a setting on the publication, under article properties:  "Action if name is in use" which I changed to "Keep existing object unchanged".
This lead me to receive a different error on synchronisation:
     "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 log"
I'm just not sure if that is a step forward or backward. This message sounds like a blanket error to cover the true error message. I will try to find out how to turn on the verbose history log.

Answer 4

I notice that the agent starts, then it applies the snapshot to the sbuscriber, and them it tries to "Bulk Copy data into table sysmergesubsetfilters'. Then the error occurs.