SQL Compare Tables without DML triggers

Sean_B

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?

sam.blackburn

Have you tried the ignore triggers option?

Sean_B

Yes, it does not work with DML Triggers.

w

sam.blackburn

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.

Sean_B

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.

sam.blackburn

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?