Lately I’ve been delving more and more in to the SQL Server world, as it pertains to Business Intelligence. Coincidentally, our DBA complains of not sleeping at night, and seems to have developed a nervous twitch. I keep telling him to lay off the caffeine, but oh well I digress.
One of the things that I’ve found irritating is the inability for long running T-SQL scripts to be able to keep the user (in other words, ME) updated as it progress through. Instead it seems to save up any print or select messages until the entire job is over then prints them out in a big explosion, not unlike an episode of Mythbusters.
Mladen Prajdic on his “I want some Moore” blog came up with a brilliant solution. Use RAISERROR, with the NOWAIT option and a low severity, to flush the message buffers immediately. To quote those two guys from the Guinness ads, Brilliant! But hey, I don’t want to steal Mladen’s thunder, go read it for yourself:
True, I doubt I’d suggest this for production code that would run unattended, but for those long scripts we all wind up writing to do some tests or fix some bad records well… you can bet this is a handy tip I’ll be using over and over.