What are the challenges you face when working across database platforms? Take the survey
Options

SQL Compare Tables without DML triggers

Sean_BSean_B Posts: 11 New member
edited March 6, 2017 5:19AM in SQL Compare
I have been trying without success to get an sql compare task to run on the command line that compares tables in two databases while excluding any triggers.

There are triggers in our production databases, but they are disabled in test. Is there some special trick to get redgate to NOT report the dml trigger differences in a command line sql compare job?

Comments

  • Options
    Have you tried the ignore triggers option?
    Software Developer
    Redgate Software
  • Options
    Sean_BSean_B Posts: 11 New member
    Yes, it does not work with DML Triggers.

    w
  • Options
    If Compare is reporting a difference when the tables are otherwise identical, this sounds like a bug.

    Could you script out the tables in both databases using SSMS and also let me know which Compare version you're using? This should be enough to try and reproduce the issue.
    Software Developer
    Redgate Software
  • Options
    Sean_BSean_B Posts: 11 New member
    It's not a bug from what I can tell. When you compare two tables that are identical except table 1 has a trigger attached to it and table 2 does not the tables come up different.

    I would like to know how to add a switch to ignore the triggers entirely when making table comparisons.

    None of the switches/options seem to work correctly.

    This is not hard to reproduce. Just take two identical tables and add an insert trigger on one of them then compare them. Try to compare them without the trigger being a factor.
  • Options
    CREATE TABLE Water (id INT, txt NVARCHAR(MAX))
    GO
    CREATE TRIGGER SayHello ON Water AFTER INSERT AS PRINT('Hello!')
    GO
    INSERT INTO Water (id, txt) VALUES (1, 'sadf')
    

    Running all 3 statements on the left database and just the CREATE TABLE on the right database, I see a difference when using default options. The difference goes away (and the trigger is no longer visible in the diff) if I use "Ignore DML triggers."

    Could you script out both tables in SSMS so I can try to reproduce the behaviour you are seeing?
    Software Developer
    Redgate Software
Sign In or Register to comment.