How to find the erroneous code failing the SSDT publish?

All we need is an easy explanation of the problem, so here it is.

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:

Very generic error

The executed script doesn’t exist in the publish script:

It said it ran this code

And this is where it navigates in the publish script upon double clicking the error message:

The apparent error line according to publish

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?

SQL Server Profiler trace results unhelpful

(I verified this statement was not the culprit)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 with GO‘s. This then leaves me with the ideal place to insert the RAISERRORS to pick up on progress. Alternatively you should stick the RAISERROR 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:

RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply