Every time something fails the publish, assuming it passes the preview generation, the error line and executed script sample are completely useless at helping me trace the source of the error, since the line number is relative to the last GO
statement, and the executed script doesn't always appear in the publish script.
In this instance, it fails with this error:
The executed script doesn't exist in the publish script:
And this is where it navigates in the publish script upon double clicking the error message:
Usually I am able to trace it down based on the error information, but this time I'm at a loss because the only relevant keywords I can think of are so common that it seems hopeless.
It looks like logging is hackish and the errors sometime come before the postdeployment scripting (e.g. table triggers executing on incomplete data since publish settings only include an option to disable DDL triggers).
I've tried running SQL Server Profiler during the publish but that tool omits even more crucial debugging information (and gets overwhelmed by my large database project). Perhaps another reason to upgrade to get the Extended Events Profiler?
(I verified this statement was not the culprit)
Best Answer
I would use
RAISERROR WITH NOWAIT
in the post-deployment script at the start of each block being executed. This way you should be able to see how far the deployment got before getting to the failure point and hopefully have narrowed it down to a particular script.How I tend to structure my SSDT projects is that I have a single post-deployment script and then include other files using
:r
and separate them withGO
's. This then leaves me with the ideal place to insert theRAISERRORS
to pick up on progress. Alternatively you should stick theRAISERROR
at the top of each included file.https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017 key point in the link being:
Sorry - this is assuming the error is within your post-deployment script. After re-reading I've realised your error seems to be prior to that.