?
Solved

SQL Server 2005 View Dependencies Bug

Posted on 2006-11-21
1
Medium Priority
?
403 Views
Last Modified: 2008-02-01

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
Comment
Question by:purplesoup
1 Comment
 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 17986335
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question