Solved

SQL Server 2005 View Dependencies Bug

Posted on 2006-11-21
1
393 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

828 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