Solved

SQL Server 2005 View Dependencies Bug

Posted on 2006-11-21
1
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

632 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