In the last Event Handling post, I looked at handling SQL Server events synchronously with DDL triggers. However, there are many events that can't be handled synchronously within the scope of a transaction--DDL triggers can't be used for those. Take the AUDIT_CHANGE_DATABASE_OWNER event, for instance. If you attempt to create a DDL trigger for it, you'll get this error:
I ran into a problem with a DBCC command recently. It involved an application running index maintenance on a daily basis. I can appreciate what the software vendor was trying to do. They probably sell their product to a lot of organizations that don't
My personal experience with PASS events is somewhat modest. I've attended the PASS Summit conference twice, I've only been to one SQL Saturday, and attendance at my local PASS chapter spans less than two years. In spite of my limited experience, I've come to this conclusion: code samples and/or live demos are better than PowerPoint slides. Now, this may be a contentious topic. I want to stress that this is my personal opinion. You may decide I'm dead wrong. So keep that in mind and please continue reading.
PASS Summit 2016 is coming very soon. If 2016 is going to be your first time at the conference, you may be looking for (or have already received) some advice. I've been seeing quite a bit of advice myself, much of it quite good. I do suspect that most of the advisers are seasoned veterans with many years of conference experience under their belts. I'd like to offer a different perspective. 2015 was my first PASS Summit, so as a sophomore, my point of view might be a little different. Hopefully, it will be relatable to you, the first-timer.
When you're faced with a new task or some other SQL Server challenge you've not tackled before, what's your go-to strategy: Author your own solution? Or use someone else's? There's no right or wrong answer. Each option comes with its own pluses and minuses. The more interesting question is this:
In the last post, we explored a couple of examples of using Extended Events to enhance T-SQL error handling. There was some potential there. But a hard-coded SPID was necessary: we couldn't use the code examples for anything automated. It was cumbersome, too. Let's change that, shall we?
In the previous post, I examined a couple of error handling scenarios in T-SQL that don't work as expected with TRY...CATCH. The first problem involved multiple errors for a single statement in the TRY block: when control transferred to the CATCH block, only one error was accounted for. The second problem involved multiple errors caused by DBCC CHECKDB on a corrupt database. The TRY block "ate" all the errors and control never transferred to the CATCH block, leaving the impression there were no errors at all. Now let's look at a way to enhance error handling with Extended Events to overcome the shortcomings of TRY...CATCH for the two scenarios.
I've known for quite a while that there are issues with TRY...CATCH for T-SQL. I've had my share of frustrations with it, and I am clearly not alone. Just check out some of these Microsoft Connect items: 1 2, 3, 4, 5, 6, 7, 8, 9, 10. Five of them are Closed As Won't Fix, three of them are Active (all three were opened more than 8 years ago), and the other two are Closed By Design.
In my previous post, I defended the use of linked servers for administrative purposes. Now let's look at some practical uses. The basic concept is to connect to the CMS, iterate through sys.servers, select data from the remote servers, then return the data for all linked servers as a single data set. Let's begin with an example that returns a list of databases on all of our linked server SQL instances.