Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

SQL Server 2005 View Dependencies Bug


Any time anyone edits a view in our SQL Server 2005 database that has dependent views this can and generally does seem to mess up the dependency tracking in SQL Server 2005.  

Because our upgrade process uses the dependencies to ensure it creates all the views in the proper order, the general impact of this is that it causes the upgrade to fail.  

When this problem happens, it can take more than an hour to recover from the failure, as we have to fix the views, and then regenerate the upgrade scripts.

Our current workaround is to ask anyone who edits a view to check for dependencies and manually re-build all dependent views. Can anyone think of a better workaround?
0
purplesoup
Asked:
purplesoup
1 Solution
 
NightmanCTOCommented:
I know - it's a royal pain in the ****. I was hoping that this had been resolved in SQL 2005, but no luck. Because this would require a recompile of the dependat objects (specifically views, functions or stored procs) I believe that MS deemed it more efficient to just break the link! Currently there is no workaround.

I use the following script to detect whether an object is referenced by another object before I edit/alter.

SELECT
  so.name,
  so.id,
  so.xtype
FROM
  sysobjects so
INNER JOIN syscomments sc ON  sc.id=so.id
AND sc.text LIKE '%YourObjectName%'

Obviously if the name is commonly used across multiple objects (i.e. ID in a column name) it returns a lot of hits.

Cheers
Night
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now