Recently I was off on a long leave from my work. After returning to office, I found that there are so many emails accumulated in my inbox, and many of them contained SQL script files to be executed on my local SQL Server test databases. Extracting each file into a folder and then executing each of these files (*.sql files) on each database was just like the task I always hate. There were some hundred of these attachments, and each file had to be executed on about 6 databases. So if I do everything manually, it will take me days to finish this.
So I set off for making a script that will automate this.
Analyzing the problem
There are two parts of the problem:
- Extracting all attachments into a folder on disk.
- Execute the *.sql files on all the databases installed on my local SQL Server.
I decided to make a VBA script in Outlook to get the attachments of my choice into a folder on disk. And a SQL script to execute all those files on all the databases.
Hopefully these two scripts will help those who are in a similar situation like I was in, or need something similar for whatever reasons.
I’ll post the solution in two parts so that this article doesn’t look messy:
- A way to extract email attachments from emails into a folder on harddisk.
- A way to execute a set of sql script files (*.sql) on a set of SQL Server databases.
There are a few assumptions I’ll make:
- The VBA script was made using Outlook 2007. It should work with other versions too. But just let me know in case it doesn’t and I’ll make the necessary fix.
- The naming convention of your SQL script files is such that when sorted ascending alphabetically, the one that needs to be executed first comes on the top. If this is not true in your case, you might need to do some workarounds, before this solution works perfectly for you, or modify the script accordingly.
e.g. My filenames are in the format DB###_ScriptName.sql, where ### is a running sequence number.
- The following video will show how easy the task became after implementation of this solution:
- (The video is intentionally in low quality format, and is meant for the purpose of this demo only. It is meant to give you an idea about how easy the task became after automating this process. In the actual scenario it was more than 500 SQL files.)
- Extracting Attachments from Selected Emails
- Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases