Red Gate forums :: View topic - Synchronize PERSISTED computed column with function
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Synchronize PERSISTED computed column with function

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Powlin



Joined: 16 May 2007
Posts: 32

PostPosted: Thu Oct 18, 2012 9:03 pm    Post subject: Synchronize PERSISTED computed column with function Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Mon Oct 22, 2012 4:31 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group