DTS Checking Constraints

#1

I have a db with a number of Foreign Keys (all with the Check Constraints
UNCHECKED)

When I run my DTS package it seems to be ignoring this and checking the
constraints.

How can I disable this when the FK is created from my DTS package ?

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

#2

Hi Michael,

We should use NOCHECK instead of UNCHECKED when altering a existing
constraint to be invalid. I made a sample on my side with DTS and it works.

CREATE TABLE Test2
(
TID INT Primary KEY,
CID INT IDENTITY(1,1) UNIQUE
)

INSERT Test2 VALUES (11)
INSERT Test2 VALUES (12)
INSERT Test2 VALUES (13)
INSERT Test2 VALUES (14)

--SELECT * FROM Test2

CREATE TABLE Test2Child
(
TCID INT FOREIGN KEY REFERENCES Test2(CID),
TC CHAR(1)
)

INSERT Test2Child VALUES (55,'B')
--The statement above will fail
ALTER TABLE Test2Child NOCHECK CONSTRAINT FK__Test2Child__TCID__48CFD27E

INSERT Test2Child VALUES (55,'B')
--The statement above will do since we have altered NOCHECK
--Use a DTS package to do the test, it also works

NOTE that if you have specified NOCHECK for the constraint, it will not
check the constraints any more.

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.

#3

Are you Cheng Ming Qing?
--
Load the whole world into One warehouse and Mine it whatever you want

"Michael Cheng [MSFT]" wrote:

Quote:

Hi Michael,

We should use NOCHECK instead of UNCHECKED when altering a existing
constraint to be invalid. I made a sample on my side with DTS and it works.

CREATE TABLE Test2
(
TID INT Primary KEY,
CID INT IDENTITY(1,1) UNIQUE
)

INSERT Test2 VALUES (11)
INSERT Test2 VALUES (12)
INSERT Test2 VALUES (13)
INSERT Test2 VALUES (14)

--SELECT * FROM Test2

CREATE TABLE Test2Child
(
TCID INT FOREIGN KEY REFERENCES Test2(CID),
TC CHAR(1)
)

INSERT Test2Child VALUES (55,'B')
--The statement above will fail
ALTER TABLE Test2Child NOCHECK CONSTRAINT FK__Test2Child__TCID__48CFD27E

INSERT Test2Child VALUES (55,'B')
--The statement above will do since we have altered NOCHECK
--Use a DTS package to do the test, it also works

NOTE that if you have specified NOCHECK for the constraint, it will not
check the constraints any more.

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.

#4

Hi,

Welcome to MSDN Managed Newsgroup!

Yes, I am Cheng. If you have any questions or concerns, you are welcome to
raise it in the newsgroup.

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS Checking Constraints

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS Checking Constraints - 10-21-2005 , 11:23 AM

RE: DTS Checking Constraints - 10-22-2005 , 02:16 AM

RE: DTS Checking Constraints - 10-26-2005 , 01:42 AM

RE: DTS Checking Constraints - 10-27-2005 , 01:36 AM