Triggers won't sync in SQL Compare 6

Ozzie

Is there anything special I need to do to make the triggers sync up with your product? It appears they are evaluated in the way that they are applied, so if I have 2 databases with the triggers applied in different sequence, SQL compare flags 'triggers' as a mismatch.

Any tricks to getting this to work correctly?

Also, it seems to not recognize new 2000 database instances, eg DEVTESTSERV/PT2RS. "An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote conections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) - what does this mean for SQL 2000?

Best,
Doug

Michelle T

SQL Compare shouldn't be comparing the order of trigger application when it decides whether to flag an object as 'Identical' or 'Different' in the main grid. However, if you look at the SQL Difference pane, you may see triggers looking 'different' on account of being in a different order. This is because the SQL Difference pane just takes the SQL creation script that SQL Server has for the object and does a textual comparison, not the more sophisticated comparison that SQL Compare does internally - this is for performance reasons.

If the object is flagged as 'different' in the main 'grid', then there is almost certainly some other difference in the object than just the order of application of triggers. (If there is no other difference, we'd like to know about it, because that would be a bug!)

As for the server error message, that's the generic error message that SQL Compare gives for 'I can't find the server', no matter what the server's version is. The mention of 2005 is just a troubleshooting tip that might apply if you're connecting to a 2005 server - back when 2005 was new, we had a lot of support calls which were resolved by enabling remote connections on 2005 servers.

Can you connect to the server with Query Analyzer or Management Studio from the same computer and using the same login method as you are trying to use for Compare? (The normal cause of that error message is either mistyping the server name or some kind of networking problem - SQL Compare just can't even find the server to talk to it.)

lfagetti

I've a table with 2 triggers.
They do match (checked with Textpad CompareFile), but Sqlcompare 6 shows 1 of them as mismatching;
It's showing as missing from both databases in this sequence:
1. it'is showing missing in database A
2. the other triggers is showed as matching
3. it's showing missing from database B

I've another table with 3 triggers (Del/Ins/Upd):
Instead of comparing Upd with Upd and Del with Del, on the "left side" it starts with the UPD trigger and on the "right side" with the DEL trigger creating a mismatch.
Then INS triggers are then compared and matching.

I can provide more evidence if needed.

Same issue for version 5

Michelle T

Does SQL Compare show the *table* as different (in the main section of the screen), with no other differences present in the table? If the table is shown as identical, then SQL Compare is functioning correctly and won't try to synchronize the 'differences' you're seeing.

The triggers show up as different in the SQL Differences pane at the bottom because SQL Compare is just comparing the text of the entire 'Triggers' section of the creation script, which is different because the triggers are in a different order.

'Make the SQL Differences pane comparison more sophisticated' is on our list of things to do, but it will be an enormous undertaking, so I'm afraid you probably won't see it for a while.

lfagetti

Yes, the table has other differences;

However, with a lot of tables with "fat" triggers, I'm forced to do manual comparison to be sure that they match.

What about comparing them separately from tables, object by object, as stored procedures?

Michelle T

Comparing triggers as seperate objects from their tables is another item on the list of things we are planning for SQL Compare, but again it's a major feature that will take some time to see the light of day.