SQL Compare 5.3.0.44 and triggers for tables in schemas

ababiec

I'm running into an issue where the change script created by SQL Compare 5.3.0.44 and how it deals with triggers on tables in schemas other then dbo.

it creates a statements like this...

PRINT N'Altering trigger [Billable].[trg_BillableComponent_History_Insert] on [Billable].[BillableComponent]'
GO
ALTER TRIGGER trg_BillableComponent_History_Insert ON [Billable].[BillableComponent]
FOR INSERT AS ...

which fails in SQL Server 2005 (complaining about not finding the trigger) until I modify it to include the schema name...

ALTER TRIGGER [Billable].[trg_BillableComponent_History_Insert] ON [Billable].[BillableComponent]

What is unusual, is that it properly names it in the PRINT statement but not in the ALTER TRIGGER statement.

Is there a patch or updated version that fixes this? Is this fixed in 6.x?

JonathanWatts

We have added a fix for sp_renamed objects in SQL Compare 6. So this should help you out.

Regards,

Jonathan

Michelle T

Unfortunately the patch does not help in the case where you have a user with a default schema set up, and they create certain kinds of object in your database (triggers, views, functions, stored procedures). The problem here is that SQL Server stores internally the exact text that the user submitted for these objects, and when a user has a non-dbo default schema, that text does not include the information about what schema the object is in.

SQL Compare uses this internal text directly (which lets us preserve details such as whether the name was bracketed), which doesn't work if the user who is doing the synchronization has a different default schema from the user who entered the object into the database.

I've raised a bug in our bug tracking system about the default schema issue, but I'm afraid it's unlikely to be fixed for some time, as we've finalised what's going into 6.1.

Meanwhile, the workaround is to always fully qualify names that you use in triggers, views, functions and stored procedures, and to manually qualify names in old objects of these types as you find them, as you have been doing.