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: -
我有一个DTS包,通过“复制SQL Server对象”任务引发错误。任务是将表和数据从一个SQL Server 2000 SP4服务器复制到另一个(相同版本),并给出错误: -
Could not find CHECK constraint for 'dbo.MyTableName', although the table is flagged as having one.
找不到'dbo.MyTableName'的CHECK约束,尽管该表被标记为有一个。
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.
源表定义了一个检查约束,似乎导致问题。在运行DTS包后,该东西似乎正常工作 - 表,所有约束和数据都是在目标服务器上创建的?但是上面的错误会引发,导致后续步骤无法运行。
Any idea why this error is raised ?
知道为什么会出现这个错误吗?
2 个解决方案
#1
2
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.
这表明sys表中的元数据与实际架构不同步。如果您没有看到任何其他更普遍的损坏的迹象,通过将表复制到另一个表(从oldtable选择* into newtable)来重建表,删除旧表然后重命名新表并替换约束将救命。这类似于2000的企业管理器在插入不在表末尾的列时所做的事情,因此在表的中间插入一个新列然后删除将实现同样的事情,如果你不我不想手动编写查询。
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...)
如果您看到其他类型的错误,我会对整个数据库的状态感到担忧。 (我假设你已经完成了CHECKDB命令并且错误仍然存在......)
#2
0
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.
将表复制到不同的目标SQL Server并得到相同的错误。
创建了一个具有完全相同结构但名称不同的新表,并且没有错误地复制。
删除并重新创建问题表上的检查约束但仍然得到相同的错误。
带有ALL_ERRORMSGS的dbcc checktable('MyTableName')没有错误。
源和目标数据库中的dbcc checkdb没有错误。
Interestingly the DTS package appears to: -
有趣的是,DTS包似乎: -
- 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.
因为检查约束创建时间是在表创建时间之后7分钟,即它在移动数据之后创建检查约束。这是有道理的,因为它不必在复制时检查数据,可能会提高性能。
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?
正如Godeke建议的那样,我认为系统表中的某些内容已经损坏,因为具有相同列的新表可以工作。即使DBCC语句没有错误?
#1
2
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.
这表明sys表中的元数据与实际架构不同步。如果您没有看到任何其他更普遍的损坏的迹象,通过将表复制到另一个表(从oldtable选择* into newtable)来重建表,删除旧表然后重命名新表并替换约束将救命。这类似于2000的企业管理器在插入不在表末尾的列时所做的事情,因此在表的中间插入一个新列然后删除将实现同样的事情,如果你不我不想手动编写查询。
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...)
如果您看到其他类型的错误,我会对整个数据库的状态感到担忧。 (我假设你已经完成了CHECKDB命令并且错误仍然存在......)
#2
0
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.
将表复制到不同的目标SQL Server并得到相同的错误。
创建了一个具有完全相同结构但名称不同的新表,并且没有错误地复制。
删除并重新创建问题表上的检查约束但仍然得到相同的错误。
带有ALL_ERRORMSGS的dbcc checktable('MyTableName')没有错误。
源和目标数据库中的dbcc checkdb没有错误。
Interestingly the DTS package appears to: -
有趣的是,DTS包似乎: -
- 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.
因为检查约束创建时间是在表创建时间之后7分钟,即它在移动数据之后创建检查约束。这是有道理的,因为它不必在复制时检查数据,可能会提高性能。
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?
正如Godeke建议的那样,我认为系统表中的某些内容已经损坏,因为具有相同列的新表可以工作。即使DBCC语句没有错误?