I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.
Background: I found that one view can be drop and I know for sure no one uses it. And it links to detach (unused) database. I decided to drop it. Before I drop I check the view to make sure.
CREATE View vw_obsolete_view AS SELECT [very long], , , ... FROM [unused_database].dbo.vw_obsolete_view DROP VIEW vw_obsolete_view
Now, View was dropped. It seem to be OK. However, I got a call from user that Application (ADP) got an error message. It states that view vw_obsolete_view is missing.
Since this view is not my coding and the my Application is call only my objects. So I have no idea why this cause an error. It took me an hour to figure out. I check all dependent objects. Nothing links to this view at all. I checked all ADP VBA code. Nothing relates to this view also.
I can't find what wrong with this. But I know that the problem is from dropping view because I was the only one who changes the database. So I make a new view with the same name.
CREATE View vw_obsolete_view AS SELECT 1 AS total
The error at the end user is disappear. End User is happy but I still can't drop view. I still have no clue.
Sidenote: I can't upgrade SQL Server right now.