Synchronize PERSISTED computed column with function

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Synchronize PERSISTED computed column with function

Postby Powlin » Thu Oct 18, 2012 9:03 pm

I'm trying to update a function with SCHEMABINDING that is used by a persisted computed column on a table, I always get this error while editing :

** Cannot ALTER 'dbo.getFormatDateHourOnly' because it is being referenced by object 'data_QueueingANO'.

In my dev box I remove manually any computed column, next I modify my function and after I re-add computed column.

But when I try to synchronize using SQL Compare I still get this error. This that take lot of time to remove column and re-add it, so I don't want to do it for every synchronization.

I would like to know what is the best way using SQL Compare to synchronize this type of change ?

--- Here is the declaration of the function and the table with computed column

FUNCTION getFormatDateHourOnly(@NbMinute float)
RETURNS varchar(12)
WITH SCHEMABINDING

TABLE data_QueueingANO
(
Call_ID int NOT NULL,
TimeElapsed float NULL,
TimeElapsedFormatted AS ([dbo].[getFormatDateHourOnly]([TimeElapsed])) PERSISTED
)

Thanks
Powlin
 
Posts: 34
Joined: Wed May 16, 2007 6:56 pm

Postby Brian Donahue » Mon Oct 22, 2012 4:31 pm

This is the type of thing that SQL Compare should be really good at - if you have to change a column in a table that is bound to a view or stored procedure with schemabinding, SQL Compare is supposed to drop the procedure, make modifications to the table, then recreate the procedure.

If this is not working, I suspect that the setting to include the dependent objects is not on.

The point of schemabinding is that you cannot drop a column if it's still referenced by a schemabound object (like your stored procedure). The schemabound objects would have to be dropped first.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests