This has been a problem since I started working on SQL 7.0. When I transferred database from one server to another using the Import/Export Wizard, the foreign key script that is created automatically fails when there are foreign keys where some of the data is not in the linked table. It should be a no check constraint, but the foreign key script is created the no check is added after the foreign key, but since the creation of the foreign fails, the NO CHECK is not added. But then either is the foreign key.

So my appeal goes out to the developers of SQL server to please fix this problem in the DTS wizard. I always end up having to find the FKY file, change the foreign keys the file and run it myself. This has been annoying me for YEARS! Just adding the WITH NOCHECK to the creation of the foreign key as in the example below will fix the problem.

Thanks,
Velvet

Example:
/****** Object: Table [dbo].[CisConference] Script Date: 12/10/2005 9:53:08 AM ******/

ALTER TABLE [dbo].[CisConference] WITH NOCHECK ADD

CONSTRAINT [FK_CisConference_Admin_Company] FOREIGN KEY

(

[comp_id]

) REFERENCES [dbo].[Admin_Company] (

[comp_id]

) NOT FOR REPLICATION

GO

alter table [dbo].[CisConference] nocheck constraint [FK_CisConference_Admin_Company]

GO

Hi Velvet,

Welcome to use MSDN Managed Newsgroup!

From your descriptions, I understood DTS will failed to create table script
automatically due to foreign key constraint. If I have misunderstood your
concern, please feel free to point it out.

Unfortunately, I cannot reproduce it on my side. Would you please generate
a small sample database, with which I could reproduce it on my side? I
understand the information may be sensitive to you, you may post it here or
my direct email address is v-mingqc (AT) online (DOT) microsoft.com (make sure you
have removed ONLINE before your click SEND), you may send the file to me
directly and I will keep secure.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.