Options

Newbie question - WITH NOCHECK

MartinHMartinH Posts: 82 Bronze 2
edited April 29, 2005 12:05PM in SQL Compare Previous Versions
When comparing two supposedly iqual databases I get several foreign key differences stating that the 'WITH NOCHECK' values differ.

When I use Enterprise Manager I can't see any differences when I inspect the foreign key properties. Where do I go to see these differences?

When I tell SQLCompare to update the destination database it really does do something because the next 'compare' comes up clean, but I can't see what it has done.

TIA,
MartinH.
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Martin,

    SQL Compare is probably the most authoritative answer about checking existing data on creation. Because the SQL language lets you specify WITH NOCHECK in three different ways:
    • ALTER TABLE x ADD CONSTRAINT WITH NOCHECK y
    • ALTER TABLE x ADD NOCHECK CONSTRAINT y
    • ALTER TABLE x ADD CONSTRAINT y WITH NOCHECK
    Query Analyzer only recognizes one of these when it checks the box that says 'check existing data on creation'. Even worse, there's a bug that means that doesn't even work reliably.

    I hope this helps clear the situation up a bit.
    · Share on Twitter
  • Options
    mchughcmchughc Posts: 1
    Brian,

    I am having the same problem. Can you please provide more details as to how I can validate this. If Enterprise Manager is incorrect can you please provide a SQL snippet that I can run against both Dbs to see that in fact there is a difference in the Constraint. In other words how is SQL Compare determing the difference? We are evaluating your product, and I need to prove to my mnager that your product is correct and EM is wrong. I haven't been able to find where in the data dictionary that info is stored?

    Thanks,
    Craig
    · Share on Twitter
  • Options
    Daniel HandleyDaniel Handley Posts: 214
    Here is some script that will help you.
    Run it against each database and note the diffferances.

    SELECT "table" = object_name(parent_obj),
    "constraint" = name
    FROM sysobjects
    WHERE xtype IN ('F', 'C')
    AND objectproperty(id, 'CnstIsNotTrusted') = 1
    ORDER BY 1, 2

    Thanks go to Craig who sent this in.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
    · Share on Twitter
This discussion has been closed.