Error when copying a check constraint using DTS

Question

I have a DTS package that is raising an error with a "Copy SQL Server Objects" task. The task is copying a table plus data from one SQL Server 2000 SP4 server to another (same version) and is giving the error: -

Could not find CHECK constraint for 'dbo.MyTableName', although the table is flagged as having one.

The source table has one check constraint defined that appears to cause the problem. After running the DTS package, the thing appears to work properly - the table, all constraints and data ARE created on the destination server? But the error above is raised causing subsequent steps not to run.

Any idea why this error is raised ?

Answer 1

This indicates that the metadata in the sys tables has gotten out of sync with your actual schema. If you aren't seeing any other signs of more generalized corruption, doing a rebuild of the table by copying it to another table (select * into newtable from oldtable), dropping the old table and then renaming the new one and replacing the constraints will help. This is similar to how the Enterprise manager for 2000 does things when you insert a column that isn't at the end of the table, so inserting a new column in the middle of the table and then removing will achieve the same thing if you don't want to manually write the queries.

I would be somewhat concerned by the state of the database as a whole if you see other occurrences of this kind of error. (I'm assuming here that you have already done CHECKDB commands and that the error is persisting...)

Answer 2

This error started when a new column (with a check constraint) was added to an existing table. To investigate I have: -

• Copied the table to a different destination SQL Server and got the same error.
• Created a new table with exactly the same structure but different name and copied with no error.
• Dropped and re-created the check constraint on the problem table but still get the same error.
• dbcc checktable ('MyTableName') with ALL_ERRORMSGS gives no errors.
• dbcc checkdb in the source and destination database gives no errors.

Interestingly the DTS package appears to: -

• Copy the table.
• Copy the data.
• Create the constraints

Because the check constraint create time is 7 minutes after the table create time i.e. it creates the check constraint AFTER it has moved the data. Makes sense as it does not have to check the data as it is copying, presumably improving performance.

As Godeke suggests, I think something has become corrupt in the system tables, as a new table with the same columns works. Even though the DBCC statements give no errors?