0

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)

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.

  • OK so there isn't actually a native way to report errors with debugging information? The error this time is coming from postdeployment, and I was able to isolate it to a particular stored procedure being run by a script, but nowhere did the publish results say what stored procedure the error occurred in. – Elaskanator Aug 8 '18 at 21:02
  • 1
    If it’s an issue with a post deployment script, then no. As you have encountered it takes you to the wrong place in the deployment script. Unfortunately you are left creating these manual RAISERROR so that in the event of a problem you can more easily identify where. It’s easy to manage though, you just ensure as part of the development process that these blocks are added to the start of every post deployment script. – CasualFisher Aug 8 '18 at 21:19
  • OK, so then it seems there isn't actually an "answer" to my question. What you have suggested is great advice though, so I'll let it simmer for a few days then probably accept it. – Elaskanator Aug 8 '18 at 22:01
  • I don’t believe there’s an inbuilt mechanism that “just works” to achieve this, but I’d be very happy to be proved wrong :-) – CasualFisher Aug 9 '18 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.