Replication and FKs

[gbraden]

I have set up transactional replication from a database located on a SQL Server 2000 to update a database every 12 hours on a SQL Server 2005. The snapshot agent works just fine but the transactional agent errors out on 'Could not drop object 'objectname' because it is referenced by a FOREIGN KEY constraint. The Step Failed.

I have done several things. It is recommended that when going from older server to new, that 'NOT FOR REPLICATION' be set as a column property. I done this. It seems that other databases with FK constraints are being updated just fine.

Any hints? Anyone?


Glen Braden

 

[mrdenny]

The snapshot agent works fine because all that does is dump the data to disk. The transactional agent is what actually runs the scripts and loads the snapshot. Manually delete the foreign keys (or the tables completely) and then restart the transactional agent.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[gbraden]

Not very practical for the short term. All I am trying to do is keep the new servers update at the same level as the old, once a day for a couple of months of test. Part of the test is replication between two new servers, duplicating the old environment.

Thanks for your help


Glen Braden