| Author |
Topic |
|
palmsnow
Starting Member
5 Posts |
 Posted - 2011-04-28 : 14:50:43
|
| have a replicated database using SQL Server 2008. Here is what I am trying to do. 1.Create a new table (MyNewTable) 2.Create a column in an existing Table (MyExistingTable) 3.Create a FK constraint on this column (in #2) as it points to PK to new table from #1. However when data get replicated to subscriber, i get following error in replication monitor.The schema script "script for adding foreign key constraint" could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 Foreign key "MyFKConstraint" references invalid table "MyNewTable" (Source: MSSQLServer, Error number: 1767) This script runs fine on publisher though. Some people seem to suggest that adding foreign key constraint cannot be done in a replicated environment when its created between an existing table and a new table. This is kind of hard to buy for me although I am kind of new to SQL Server replication. Any thoughts? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-04-28 : 15:05:48
|
| "We don't replicate our foreign keys."We don't either. |
 |
|
|
palmsnow
Starting Member
5 Posts |
Posted - 2011-04-28 : 15:31:02
|
| " Please ensure that all of your articles are together that have relationships, otherwise you're going to have problems".Our current process is that we just run the script on the publisher and it get replicated to subscriber. Seem like on subscriber things are not replicated in the order we are doing it on publisher. As per your suggestion above, how can we ensure it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
palmsnow
Starting Member
5 Posts |
Posted - 2011-04-29 : 10:04:12
|
| We have just one publisher and several subscribers. I guess my question is really to verify what our DBA is saying about why the problem is happening at first place(because its hard for me to belive). My script is in following order.Step 1CREATE TABLE MyNewTableID INT IDENTITY(1,1) NOT NULL................Step 2ALTER TABLE MyExistingTable ADD IDFromMyNewTable INTStep 3ALTER TABLE MyExistingTable ADD CONSTRAINT FK_BlahBlah FOREIGN KEY(IDFromMyNewTable) REFERENCES MyNewTable (ID)This scripts work fine on pubisher but it fails on Subscriber compalining that FK constraint cannot be added because MyNewTable does not exists. Per my DBA "its not guaranteed that these scripts will run with the same order on subscriber and step 3 can get executed before step 1 there". Is my DBA statment correct that subscriber can run script in different order then how it was executed on publisher? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
palmsnow
Starting Member
5 Posts |
Posted - 2011-04-29 : 13:02:16
|
| so what exactly will be the process of doing it. When a FK is created on publisher it gets replicated to subscriber. How can I make FKs not get replicated to subscribers? Also is there any best practice document describing how to handle this type of scenario? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
palmsnow
Starting Member
5 Posts |
Posted - 2011-04-29 : 16:59:33
|
| So just to be sure if I got it correct, you have FKs replication off for all tables ( existing one or new ones)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Adding foreign key constrain fails on subcriber