I would like to give my client the ability to make backups of their live SQL
Server 2000, but when I export using the export wizard, I get a foreign key
error on foreign keys where they should have a WITH NOCHECK in them because
a null or zero value is allowed in that particular field and does not match
an ID in the corresponding lookup table. SQL Server's Export wizard doesn't
create the script for this type of constraint and doesn't add the WITH
NOCHECK clause to the query. So when I export for backup before I make
changes, I have to find the foreign key file, add the WITH NOCHECK to the
script where needed and run the script myself in the SQL Query Analyzer.

My customer is not going to be knowledgeable enough to do this and she is
going to think that the export failed because of the failure reported every
this DTS package is executed. (Incidentally, I've seen this problem with
SQL Server since 6.5 and it hasn't ever been fixed despite my turning in the
bug several time to MS.)

So my question is, has someone else handled this problem out there by
changing a built in sproc and I just don't know about it? If not, does
anyone know how to get to where this script is created so that I can update
it so that it will function properly for my nocheck foreign keys? OR do I
have to drop these foreign keys altogether?

Thanks in advance!
Andrea

Dear Andrea,
My understanding of your issue is:
You encountered the foreign keys checking error when you export your SQL
Server 2000 database via Export Wizard. The error message indicated that a
null or zero value is allowed in that particular field and doesn't match an
ID in the corresponding lookup table. You need to manually add the WITH
NOCHECK statement to avoid checking the foreign keys. You want to know if
there is a way to let the export wizard not check foreign keys.
If I have misunderstood, please let me know.

I don't understand why some foreign keys fileds are allowed to be null.
Generally, such design is not recommended. Anyway, for your question, I
recommend that you add the "WITH NOCHECK" option or the "NOCHECK CONSTRAINT
constraint_name" option to the "ALTER TABLE ..." statement.
No global stored procedure can help you disable the constraint checking,
however you may create a stored procedure to do that, such as:
CREATE PROCEDURE sp_removecheck
(@tablename varchar(100),
@constraintname varchar(100))
AS
declare @strRemoveCheck varchar(400)
set @strRemoveCheck = 'ALTER TABLE ' + @tablename+' NOCHECK ' +
@constraintname
EXEC(@strRemoveCheck)

Also, I think it's not necessary to have such constraints existed since the
foreign key fields are allowed to be null.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

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

Thanks you for your response, as I have posted this in the past with no
response.



Yes you understand correctly. There are various reason for allowing null or
0 in a foreign key field when the value is not required for the dataset.
When I have seen it in many databases and usually the 'Check existing data
on Creation', 'Enforce relationship for replication' and 'Enforce
relationship for INSERTs and UPDATEs' options are all unchecked. It is
necessary to have this constraint for database diagramming and was touted as
a feature for this purpose in a SQL Server User Group meeting that I
attended (I cannot remember who the speaker was). SQL server is setup to
allow this, and I have unchecked the relevant options, but DTS doesn't
handle them properly.



I have been manually adding the WITH NOCHECK option to the *.FKY file, but
SQL's DTS recreates the file every time that the DTS package is executed and
then I have the same problem of having to execute the file manually.


Thanks!
Velvet

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Dear Velvet,
I need to establish a test environment for further research.
Could you please mail me (changliw (AT) microsoft (DOT) com) a testable database
backup file and more information on reproducing your issue?

Sincerely,
Charles Wang
Microsoft Online Community Support

I would love to, thank you!
Velvet

"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Dear Velvet,
Thanks for your email.
This is a quick note to let you know that I'm performing research on your
issue. I'll give you a reply tommorrow.

Sincerely,
Charles Wang
Microsoft Online Community Support

Dear Andrea L,
Thank you very much for your email and letting me reproduce your issue.

From my test, I think I totally understand your concerns now.
I summarize your issue as following:
1. The foreign key constraint statements in the FKY file failed to be
executed if it was not added with WITH NOCHECK.
The reason is that the data in the table doesn't fit the constraint with
check, so you need to use the WITH NOCHECK option when you try adding the
constraint on the Users table.
2. DTS failed to export data when the type_id=0
I think you selected the "Copy objects and data between SQL Server
databases" option and the "include extended properties" option. In that
case, DTS will fail because it tries to create the constraint on the Users
table and find the data does not fit the constraint.
From my research, if you select "Copy tables and views from the source
database", the task can succeed, however the constraints and primary keys
won't be generated at the destination tables.

I recommend that you remove the constraints on the table, essentially based
on the two points:
1. Since the foreign key field of the Users table is allowed to be NULL and
other values out of the foreign table's, the meaning of create such a FK
constraint is not too big. Generally we expect FK constraints are used to
ensure keys' consistency between two tables.
2. Even without the FK constraint, your daily operations on these two
tables should not be influenced. You can also use join statement to query.

Sincerely hope my suggestions are helpful to you.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support