Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 View Dependencies Bug

Posted on 2006-11-21
1
Medium Priority
?
399 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 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

704 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