none
SQL2008: Bogus/misleading 547 error message?

    Question

  • I am getting a 547 error while executing a DELETE query within an Instead Of Trigger on a table. Obviously I could be misreading the data; however I've examined the table contents and the corresponding foreign table and there is no record in the foreign table whose key value matches that of the primary table key; thus I believe the reference constraint error is not valid. At the same time I find it unlikely that SQL 2008 is randomly throwing a referential constraint error.

    I'm not a SQL guru, just a developer trying to test some stored procedures and triggers and at this point am pretty much baffled.  I'd appreciate any help/pointers on how I might debug this. Is there a way to get more information out of SQL on such an error? I'm already single stepping through the code, but when I hit this DELETE there's nothing to step "into" (as there's no trigger associated with a DELETE on that table.)

    Thanks in advance for any help!

    Bill


    Bill Cohagan
    • Edited by Bill Cohagan Monday, May 10, 2010 10:06 PM Make title more descriptive
    Monday, May 10, 2010 10:01 PM

All replies

  • A couple of things you might do.  First, make sure that all the tables are referenced with the schema name in your trigger, and also fully qualify the table names when you are examining the table contents.  That is, make sure that it is not the case that you and your trigger are looking at one table, but the foreign key is looking at a different table in a different schema with the same name.  Second, you could see this error if your database is broken.  So do a DBCC CHECKTABLE on both the parent and child table.  Note that if the tables are large, DBCC CHECKTABLE can take a while.  If that is going to be a problem, you can dump and restore your database somewhere else into a development environment and do the checks there.  If this database was at one time on SQL Server 2000 or earlier, include the WITH DATA_PURITY option.  (If you are not sure, include the DATA_PURITY option).  If you want to check the whole database, you can do a DBCC CHECKDB WITH DATA_PURITY, of course, that could take some time if you have a large database.  

    Tom

    Monday, May 10, 2010 11:51 PM
  • Tom

      Thanks for the quick response.  I ran the DBCC CHECKDB WITH DATA_PURITY as my database has only a small amount of data in it. There were no errors. I will take your suggestion to fully qualify names; however there is only the one schema, dbo. I am using SQL Server Management Studio to dump the data so I'm sure I'm looking at the intended data. The table references in my stored procedures are NOT fully qualified however so I'll change that and see if it helps.

    Thanks again,

    Bill


    Bill Cohagan
    Tuesday, May 11, 2010 4:03 AM
  • Bill,

    Can you post CREATE TABLE(s), INSERT INTOs for population and TRIGGER / other code?

    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, May 11, 2010 5:41 AM
    Moderator