SQL Compare 5.3.0.44 and triggers for tables in schemas
ababiec
Posts: 2
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...
which fails in SQL Server 2005 (complaining about not finding the trigger) until I modify it to include the schema name...
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?
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?
Comments
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd
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.
Redgate Software