How can we help you today? How can we help you today?

Newbie question - WITH NOCHECK

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.
MartinH
0

Comments

3 comments

Sort by
  • Brian Donahue
    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.
    Brian Donahue
    0
  • mchughc
    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
    mchughc
    0
  • Daniel Handley
    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
    0

Add comment

Please sign in to leave a comment.