search

chadbaldwin / chadbaldwin.github.io

The repo that drives my blog chadbaldwin.net
http://chadbaldwin.net
31 stars 36 forks source link

[Comments] Cheatsheet - SQL RAISERROR #6

Open chadbaldwin opened 3 years ago

chadbaldwin commented 3 years ago

https://chadbaldwin.net/2021/01/15/raiserror-cheatsheet.html

dantheother commented 3 years ago

Another cool feature of RAISERROR is you can capture the output from calling code if you're using an sqlConnection object. Here's an example in c# https://stackoverflow.com/a/23774727/11569, there's powershell examples out there too. Great way of getting messages to the console/ui (I guess console is a UI) for really long running stored procedures. Or for logging extra info about stored procedure executin.

chadbaldwin commented 3 years ago

@dantheother yup! It's awesome, that's probably my number 1 use for it. Every stored procedure I write is packed with them.

Messages get pushed to a buffer, but the buffer only occasionally gets flushed to output, and using NOWAIT will force a flush. I cover this at the top with a couple examples including substitution parameters to add a log date.

chadbaldwin commented 3 years ago

@dantheother I decided to add in a demo specifically for this, as it really is one of my favorite uses and should be included in this post. Thanks for the comment!

samot1 commented 3 years ago

RAISERROR WITH NOWAIT runs into buffering problems too (but later than the usual PRINT).

See my question / demo at https://dba.stackexchange.com/questions/270729/ssms-sql-server-delays-console-output-raiseerror-with-nowait

Print starts to buffer after 40 lines, RAISERROR starts after 500 lines to buffer 50 lines. So even RAISERROR is not the perfect solution for loops or much speaking stuff (imaging the use of Ola Hallengren's Maintenance procedures when you run eg. Index optimize over 100 databases with tons of tables each)

chadbaldwin commented 3 years ago

@samot1 Yup, I run into this limit all the time, but didn't feel the need to get into that level of detail as the 500 message cutoff, then switch to every 50 is sufficient for the majority of people. A few sections of this post probably could have been blog posts of their own, heh. The main goal is to encourage developers to improve the output on their code to aide readability and monitoring progress, there will always be outliers.

This is also the reason I recommended the interval option by only outputting messages in intervals rather than for every iteration of the loop.

That's a nice demo btw, I like that.