URGENT: Updating a view with a computed column
Posted on 2006-06-30
We're currently using views as the connection points to key tables in other database on the same database server. This is done to make development and management easier and to ensure that should any table location need to be updated, it can be done all from the one place. For example:
DB1 has a table call 'Products' (dbo.Products)
DB2 has a stored procedure which requires the data in Products. Instead of connecting to products by DB1.dbo.Products we have create a view which connects as DB1.dbo.Products and then in the stored procedures, we reference the local view as dbo.Products. This ensures that if Products was ever to move or be renamed we would only need to update the view to ensure consistency.
(NB: I note that this may not be many dba's preference in working methods, please keep in mind that this is a snap shot of a setup where in we have a few hundred databases and many thousands of stored procedures all relational, so management needs to be kept as simple as possible and movement of some/many tables will be common as data expands across more servers in the setup)
The issue that has come to hand is this, one of the tables we reference remotely, needs to be updated, only on two columns. Once of these columns participated in a 'Computed' column in the source table. This is what appears to be the cause of this error:
Server: Msg 4406, Level 16, State 2, Procedure UpdateTest001, Line 1078
Update or insert of view or function 'dbo.Products' failed because it contains a derived or constant field.
We do not use any triggers on the table and this single computed column is very much a required item, so I would like to ask if there is anyone out there who may have came across this issue before and/or anyone who may have a work-around for this issue.
Thanks in advance.