3

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?

Daryl1976
  • 191
  • 4

1 Answers1

4

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

Daryl1976
  • 191
  • 4