DROPPIN PRIMARY TABLE..

Question

Post reply

DROPPIN PRIMARY TABLE..

Ram:)

SSCrazy

Points: 2246
More actions
July 1, 2010 at 3:11 pm

#141842

Hi,
We are using sql 2000 and migrating to 2005.
We have dts packages that will drop tables in the destination database then it will restore tables from source database. For this we are using Transfer SQL Server Objects Task tool in dts package.
The tables which we are dropping and restoring again, are Primary tables and have references to few tables.. While using dts package we just selected This parent tables but not child tables.
The problem is when we run this dts package from sql 2000, It is dropping this table with considering the foreign key constraints.
we migrated to sql 2005 now it is showing error, that cannot drop as it have foreign key constraints..
Is it a mistake from our side or is the bug in sql 2000
Ram...
🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ram:) SSCrazy Points: 2246 More actions · Answer 1

Any body to give comments on this..

as i don't know it is the right platform to post this problem or is my problem idiotic

🙂

SQLDCH SSChampion Points: 11640 More actions · Answer 2

Can you use the NOCHECK option on the constraint?

I. Disabling and re-enabling a constraint

The following example disables a constraint that limits the salaries accepted in the data. NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow for an insert that would typically violate the constraint. CHECK CONSTRAINT re-enables the constraint.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Ram:) (7/1/2010)
Any body to give comments on this..
as i don't know it is the right platform to post this problem or is my problem idiotic

Only 24 minutes went by... I'm not sure why folks think asking questions on a forum is like pressing the ol' {f1} key. 😉

My question would be... if the intent is to drop these tables and rebuild them with FK's in place, why aren't you also updating the child tables?

The "simple" and most correct "fix" would be to do a proper "merge". Search for TableDiff.exe on Google... it's certainly one easy way to do it. Another way would be to just write an "upsert" in TSQL. It's not that hard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)