Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Merge Replication Failing With Error 'The scheme script could not be propogated to subscriber'

Ok, I've had merge replication set up for a long time on my two SQL 2000 servers.  The only thing I changed was I added a Diagram on the primary database (DB01).  Ever since I did that, my replication fails when I try to push the subscription with this error:

'The schema script \\servername\MSSQL\ReplData\unc\servername_dbName_dbName_Publication\20090209210207\tblNotes)132.dri could not be propagated to the subscriber.'

I'm at a complete loss as to what this error means.  I've tried restoring the database from DB01 onto DB02 and still no luck. I've even tried deleting the database from DB02 and creating a new one when pushing the subscription.

Any help?  Ideas?
0
tphelps19
Asked:
tphelps19
  • 3
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Does both of your servers have access to the share path mentioned.

Check this out:
http://support.microsoft.com/kb/318898c
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22449447.html

0
 
tphelps19Author Commented:
Yes, they both have access.  I ran the output file like the other article says and here is what came out in the log:

The schema script '\\SSDDB01\MSSQL\ReplData\unc\SSDDB01_ERdb_ERdb_Publication\20090210095804\tblNotes_132.dri' could not be propagated to the subscriber.
[2/10/2009 10:04:40 AM]SSDDB01.ERdb: {call sp_reinitmergesubscription (N'ERdb_Publication', N'SSDDB02', N'ERdb', 'FALSE')}
Reinitialized subscription to publication 'ERdb_Publication' at subscriber 'SSDDB02:ERdb'Percent Complete: 0
The subscription has been marked for reinitialization. Reinitialization will occur the next time you synchronize this subscription.
Repl Agent Status: 3
Percent Complete: 0
The schema script '\\SSDDB01\MSSQL\ReplData\unc\SSDDB01_ERdb_ERdb_Publication\20090210095804\tblNotes_132.dri' could not be propagated to the subscriber.
Repl Agent Status: 6
Percent Complete: 0
Category:NULL
Source:  Merge Replication Provider
Number:  -2147201001
Message: The schema script '\\SSDDB01\MSSQL\ReplData\unc\SSDDB01_ERdb_ERdb_Publication\20090210095804\tblNotes_132.dri' could not be propagated to the subscriber.
Repl Agent Status: 3
Percent Complete: 0
Category:SQLSERVER
Source:  SSDDB02
Number:  547
Message: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblNotes_tblMembers'. The conflict occurred in database 'ERdb', table 'tblMembers', column 'cust_number'.
Repl Agent Status: 3
Disconnecting from Subscriber 'SSDDB02'
Disconnecting from Subscriber 'SSDDB02'
Disconnecting from Publisher 'SSDDB01'
Disconnecting from Publisher 'SSDDB01'
Disconnecting from Distributor 'SSDDB01'


Note the part that says "..FOREIGN KEY CONSTRAINT"       This is the one thing that changed prior to me doing the replication.  I had it up and working fine, then I deleted my publication and made some changes to my primary (DB01) database.  Part of this change included creating a SQL diagram with primary keys pointing to what they referenced in foreign tables.  It works fine on DB01, so I can't imagine why that would be a problemin replication?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I hope it is because of the data present in that database.
Try fixing that Foreign Key constraint in that database and then try distribution.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tphelps19Author Commented:
Well that's the problem... how do you go about fixing that?  I don't know how to write a query that says, show me all tlhe records in tabl1 that do not match a record in table2.  Is that possible?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Sure.. You can do that..

Assume table1 has pk which is referred in table2 as FK.

then the query below will help you to identify which data creates problem for you:

select table2.*
from table2
where table2.fk not exists (select pk from table1 where table1.pk = table2.fk)

Once you identify those records, you need to mitigate that and create foreign keys back.
And if you have created your Foreign key again, then your problem is resolved right.
0
 
tphelps19Author Commented:
That did it!  Basically I found out from the logging put into the agent that it was constraints on the primary keys / foreign keys that I had set up from the diagram.  Inconsistent data where we had previously deleted rows months ago and since it was linked as a primary/foreign key relationship, it was failing the replication.  Thanks again!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now