Automating automation

If your shop is big enough, you probably have many automation jobs and may easily have tens of different job steps, that you schedule to run, say, nightly. How do you check the execution status? Here's a short gist, that would take the execution results of all the steps, check if there were any errors and then send an email to all needed recipients listing all the failed steps.

To improve things further, you can split your steps into two or more jobs, based on their importance, or include more information in the notification email, like links to specific urls, that are relevant to your task at hand.


Manually syncing data from an external data source

In our environment, we have several linked servers defined for our MS SQL central DB. One or two times a day we need to pull data from these external data sources and store it in a local table for further processing. All records have timestamps, but we cannot rely on time being in sync between the MS SQL and linked servers. Moreover, some records can be recorded at a later time, yet having earlier timestamps.
So, here what we did to come up with a workable solution in these constraints. First, we use the current time offset back as a parameter to call to a linked server. The offset is sufficient enough to guarantee we will get all the records we did not see before. Second, we'll use EXCEPT to skip the records we already have in the local table. Here's the gist:

Another point worth to mention, yes, you can use insert ... Exec sp_executesql @sql_string to insert results of stored procedure execution into a table.