I was surfing on twitter (almost do not like doing that) when I saw a message from one of the big names in the SQL Server community on the world , Sir Robert Davis (@SQLSoldier) asking for a solution to export SQL Server queries to Excel, maybe using Powershell.
As most of the time I’m intruding on others’ conversations on twitter (I know I must stop this) I shoot a DM to Robert asking if I could help with anything. Actually I had nothing done, need to do.
After some emails and a lot of DM´s with my questions (sorry man, Sometimes I need to draw for understand) I could get what Robert wanted (at least I think).
Robert has his clients and he need a simple solution to gather information quering a repository database that he has in each client and the information should be in a excel report. BUT the report must be in worksheets at the same Excel file.
Of course I will not show what querys that Robert use and I beleive that the script that I sent to him is specific to his job. But I worked in a new one that can be used for anyone.
The idea is that you have .sql files in a folder with the querys that you want to gather. Remember that this querys can be any kind of the T-SQL Script, but always with one resultset.
Then we just will load this files and report them into a Excel file, split into worksheets.
The PowerShell Solution must be simple, objective and functional as the person who will run it on the client does not need to have ANY knowledge in PowerShell. Just Click, maybe input some informations as Server Name, Database Name, User Name , PassWord to connect. Only this. Other point to be observed is that the computar that will run the PowerShell script does not necessarily need to have the Sql Server (or only client) or SMO installed . At least .Net framework I believe it will. So we will use Net for to do the job.
First Lets Create a folder to our .sql files and the posh script called HealthCheck, c:\healtcheck. In my case this folder is in hardcode inside the script and of course I could do this as well , but for me the more conditions that I put in the script, more will be possible for errors. But it is up to you.
Now lets take a look in the scipt :
The next Step is to create a .bat file calling the script.
The Region Functions has the that I need to my script. There is 3 functions :
Write-ScriptLog – I create this function to log any kind of error in a .log file located at the same folder that the others files (.sql and .ps1)
These functions are in the PowerShell.com Script Library and I am using to show a message when the Script finishes or a connection failed. Also it is logged in the file .
In the Script Region :
I am using the parameters as Mandatory to input the information about the connection (SQL Server,Database, User Password).
Then I stop all processes that are using Excel. Why I am doing this ? when you work with Excel (COM Obect) and for some reason the script stop (or crasch – whatever) unless you close it, the process still be active and locking the excel file. The script has a code to delete the file if it exists and if you o run the script again a message will be displayed :
Remove-Item : Cannot remove item C:\healthcheck\ HealthCheck_2012_05_09.xlsx: The process cannot access the file ‘C:\sqldm
repository\ HealthCheck_2012_05_09.xlsx’ because it is being used by another process.
At C:\healthcheck\GatherResultSets.ps1:78 char:17
+ Remove-Item <<<< -Path $pathTest -Force
+ CategoryInfo : WriteError: (C:\sqldmreposit…2012_05_09.xlsx:FileInfo) [Remove-Item], IOException
+ FullyQualifiedErrorId : RemoveFileSystemItemIOError,Microsoft.PowerShell.Commands.RemoveItemCommand
Be in mind that it is better when run it, does not have any excel opened. It will be closed.
Then it is just the code to load the files and report to Excel.
The Excel report will be created at the same folder and with the name HEALTHCHECK_yyyy_MM_dd and the worksheets will have the same name as the .sql files.
In order to not send to Robert a BIG Script and to be simple for him to use and configure in his clients (to not create module..etc) I create an function and put the excel code inside it. But if You have your own environment I suggest you download this amazing function wrote by Luc Dekens(@lucd22) Beyond Export-Csv: Export-Xls and add at your module function.
To automate you can use a SQL Server job step type cmd and run this bat. Just pass the parameters sqlserver, database, user and password. You can change the code to accept windows authentication only in order to not show the credentials in the .bat.
This is an idea. You can customize to fit what you need !!!
I did a video showing th use of the Posh Script, and for the .sql scripts I used some dvm´s that I found in this Amzing (as always) resource from RedGate :