I am tracking down a bug in a SQLCMD migration script, when dropping unused objects.
The error I receive is the following:
Msg 208, Level 16, State 1, Server XXXXXXXXXXX\XXX_DEV, Procedure vwEmployeeCompleteAll, Line 47
Invalid object name "vwTeamsLatest".
The script in question looks like this:
USE [DatabaseName]
GO
DROP PROCEDURE IF EXISTS dbo.spPrepareDischarge
DROP VIEW IF EXISTS ....
/*a handful of other views being dropped*/
DROP VIEW IF EXISTS dbo.vwTeamsLatest
Interestingly enough, vwEmployeeCompleteAll has no dependency on vwTeamsLatest.
At first I traversed the object graph manually a couple of times to verify I did not overlook it in a cascade some levels down and then double checked using ApexSQL Analyzer. Looking at it from both point of views, neither file has a dependency on the other one.
After this I did a full text search for vwTeamsLatest and it does not appear in any used view, sproc or function.
Am I interpreting this error message wrong and vwEmployeeCompleteAll is not the right place to go digging?
The system in use is a SQL Server 2017
A good nights sleep enabled me to get some distance and rethink my approach.
Of course if twas a dependency of vwTeamsLatest, that was causing the issue. If this view wouldnt have had any other objects, that depend on it, I would have been able to delete it.
The culprit was a co-worker, who updated a function, that was using this outdated view, to a new view, but forgot to update this scalar function in the upgrade script. So the source file and the actual database where not in sync. Once I updated the function the migration run smoothly.