Generated script for Triggers

brucekillam

I've got a db where I've modified a ton of table triggers and am trying to get SQL Compare to Sync them. The compare window sees the differences and generates the script, but when have it do the sync, I get an error like:

[208] Invalid object name 'Agreement_aud'

The alter statement generated looks something like this:

ALTER TRIGGER [Agreement_aud]
  ON [App].[Agreement]
  AFTER UPDATE,DELETE
AS
      ...... SQL HERE ......
END

If I copy this alter script and modify it to look like this:

ALTER TRIGGER App.[Agreement_aud]
  ON [App].[Agreement]
  AFTER UPDATE,DELETE
AS
      ...... SQL HERE ......
END

then it works fine. Notice that all I add is the schema name. Is this a known issue? Any workarounds?

Michelle T

There is a known issue that occurs when you create 'textual objects' (objects for which SQL Server stores the exact creation text internally, like triggers) which are internally schema-qualified but not schema-qualified in the actual create statement (e.g. a user with a default schema creates them / they are created in the same batch as a CREATE SCHEMA statement).

The problem is that SQL Server stores the version without the schema in the name, then SQL Compare gets out this version and attempts to use it, but because SQL Compare is connecting as someone with a different default schema the object it's trying to ALTER is an object with the same name in a different schema.

The workarounds are:
1) Schema-qualify all your objects when you create them (I appreciate this is a little late now!)
2) If all the objects are from the same schema, tell SQL Compare to open the script in QA / Management Studio and connect with a user with default schema set to the appropriate schema, then run the script from there.
3) Go through and issue appropriate ALTER statements with schema-qualification for each affected object.

If you confirm that this is your problem, I'll add a link to this thread to the bug report, which will put it up the queue of things to fix (although I can't give any guarantees about when it will get fixed).

brucekillam

Yes, this seems be the problem and if you could add a link to this thread to the bug report, that would be much appreciated.

Michelle T

Link added - we'll be considering this problem in our next bugfix release (not 6.2, as that's already full with source control integration, but the one after that).