Sql-server – SqlPackage resolving table that doesn’t exist

sql serversqlpackage

I'm trying to validate the integrity of one of our SQL Server 2016 databases by using sqlpackage.exe to extract the dacpac and validate the schema:

 ./sqlpackage.exe  /DiagnosticsFile:"diagnostics_users.txt`"   /Action:Extract /TargetFile: "users.dacpac"   /SourceConnectionString:"connstring"  /p:VerifyExtraction=true"

When I run it, I get the output

Resolving references in schema model
Successfully extracted database and saved it to file

However, when I check the source database, there is a stored procedure which references a table in a database that does not exist. If I try to run it in SSMS, I get the error: Invalid object name '[Old_Database].dbo.VIEWER_LOG'. However, SqlPackage did not flag this as an error

When I search in the diagnostic logs, I do see the following:

Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 1 : ColumnResolver: #34027998 ResolvedDescriptor for column:
ResolvedDescriptor: [DATE_TIME_STAMP] 
Potentials(1):
    [SqlColumn : P1 - [OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP]]

Available Column Sources:
ColumnSource VIEWER_LOG (affinity = 1) with no known columns

There is a table on the target database called Viewer_Log, but, not with the schema OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP, since OLD_DATABASE.DBO does not exist. Shouldn't SqlPackage be flagging this as an error when it resolves references? Is there a configuration available to detect and flag this?

Best Answer

I found the answer in this post https://www.sqlservercentral.com/forums/topic/create-or-alter-procedure-not-detecting-invalid-object-names-1 Basically, stored procedures use 'late binding' which allows for non-existent entities to be allowed during compile time, and resolved at run time. This link also provides a helpful SQL Script to detect those issues.

I have additionally created a post-deployment script that can be used in SSDT to verify after a deployment that a given database has no unresolved references https://gist.github.com/DarylSmith/ff69ee75dc1bb61af44dc9b9d9e00d7a