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?
1 Answer 1
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