T-SQL and XML
I was wary of XML when I first heard about it - mostly because I was still trying to wrap my head around other stuff that I was learning. Therefore, I didnt learn much about it at first. Then, I needed to pass multiple values to a stored procedure and looked into it. I quickly fell in love!! There are many posts about using XML within T-SQL. I am posting one so that I can search on my own blog for this when I need it - I dont seem to keep it in my head long enough to use it the next time I need it. :)
I am going to use examples from my alerter system, that is where I am using it as I am writing this article - so 2 birds with 1 stone - cant beat that! By the way, I will format the SQL code better - it looks weird being all gray!
Using Triggers for auditing changes to sql jobs
Recently there was a job that was changed to stop at a step earlier than normal (presumably for testing). When it ran that night, it didn't run all the steps. This job has a lot of dependencies which needed to be re-run the next day and caused a bit of chaos for a few applications, data warehouse data, and reports.
In order to see who made the change, there would have to be some sort of auditing running prior to when the change happened. Auditing of sql jobs and more had already been identified as a project for our department, but hasn't been prioritized yet. This of course, doesn't help identify what happened for this specific time. :)
In the mean-time, management wants to be able to track these types of changes. Therefore, I started researching the multiple options and found some common solutions: