September 13, 2005 at 8:28 am
I have one publication with two subscriptions to it. The first subscription includes all the articles in the publication, the second only two tables (TABLE1 and TABLE2). The first one works fine when I run the snapshot agent, but the second one fails saying "Cannot truncate table 'TABLE1' because it is being referenced by a FOREIGN KEY constraint".
I have set it up so that TABLE1 and TABLE2 are not deleted during the snapshot process but they do have their data deleted. I have scripted out the creation in both subscription databases of TABLE1 and the foreign key in TABLE2 that references it, and they are identical.
I notice that the snapshot folder creates a script called snapshot.pre in the snapshot folder. This script appears to be for removing the foreign keys from the tables in question. My question is this: why does this script apparently run against the tables in the first subscription database, but not against those in the second?
Thanks
John
September 13, 2005 at 5:29 pm
If you have configured Snapshot Replication then by default it will DELETE data on the Subscriber.
How did you set up the Subscriber? It sounds like you have created the Database on the Subscriber as it is in the Publisher that has Foreign Keys. You must remember that your Subscription Database is "Logically" READ ONLY! This means that Foreign Keys are not required on the Subscriber!
Hope this helps.
September 14, 2005 at 3:05 am
I am using transactional replication, but obviously this starts off with teh snapshot being applied. Yes, I created the table on the subscriber using the exact same DDL as on the publisher (except with a NOT FOR REPLICATION statement on the identity field).
The publication allows you to export foreign key constraints, so presumably there must be some way of dealing with them. Indeed, in the other subscription database, I don't get the message and the table is truncated and repopulated without problems when the Snapshot Agent runs. I would like to keep the foreign key constraints as a way of having confidence that the data is being replicated correctly.
It seems to come down to the fact that the snapshot.pre script in the snapshot folder is being run against one subscriber but not the other - why should this be?
Thanks
John
September 14, 2005 at 5:42 am
During the set-up of the subscriber, if you are saying "INITIALIZE SCHEMA AND DATA", then the .sch scripts will be run against the subscriber. In the replication folder read the .sch script associated with the table being truncated. You'll probably see:
SET QUOTED_IDENTIFIER ON
GO
truncate table [dbo].[tablename]
then some If exists(create table....) If you don't want the data deleted select SUBSCRIBER ALREADY HAS SCHEMA AND DATA. This will prevent the .sch scripts from being run.
September 14, 2005 at 6:58 am
Yes! It does say that. But why is that table being truncated successfully in one subscription but not in the other?
I do want the data deleted from the table, but I don't want the table itself dropped.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply