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 :
1: #region Functions
2:
3:
4: function Write-ScriptLog
5: {
6: param($msg,$date,$ReportOn)
7: if ($ReportOn -ne "")
8: { Add-Content -Path (Join-Path $ReportOn "SQLDmRepository_$($ServerName)_$($date).log") -Value $msg}
9: Write-Output $msg
10: }
11:
12: function Show-MessageBox ($title, $msg) {
13: [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
14: [Windows.Forms.MessageBox]::Show($msg, $title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Warning, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null
15: }
16:
17: function Show-InformationBox ([string]$Title,[string]$Message) {
18: [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
19: [Windows.Forms.MessageBox]::Show($Message, $Title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Information, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null
20: }
21:
22:
23:
24: #endregion functions
25:
26: #region Script
27: Function Gather-Result {
28: param(
29: [Parameter(Position=0, Mandatory=$true)] [String]$Server,
30: [Parameter(Position=1, Mandatory=$true)] [String]$UserName,
31: [Parameter(Position=2, Mandatory=$true)] [String]$Password,
32: [Parameter(Position=2, Mandatory=$true)] [String]$Database
33: )
34:
35: Get-Process "Excel*" | Stop-Process
36: #$Server = '.'
37: #$UserName = 'xx'
38: #$Password = 'xxxxxxxxx'
39:
40: #Variables
41: #$Database = "SQLDMRepository"
42:
43: #Variable to Change The Folder ##################################################################
44: $ReportOn = "c:\HealthCheck"
45: #Variable to Change The Folder ##################################################################
46:
47:
48: $ConnectionFailed = $false
49: $Date = get-date -format 'yyyy_MM_dd'
50:
51: #test path . If does not exist break the code
52: if (-not (Test-Path -Path $reporton -PathType Container)) {
53: Show-MessageBox -msg "Path $($ReportOn) does not exist" -title 'Error'
54: break
55: }
56:
57: try {
58: #Connect to SQL Server using .net
59: $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
60: $SqlConnection.ConnectionString = "Server = $($Server); Database = $($Database); User = $($Username); PWD = $($Password)"
61: $SqlConnection.Open()
62: } catch {
63: Show-MessageBox -msg "Error to connect to Server $($Server) Additional information at log file" -title 'Error Connection'
64: Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)
65: break
66: }
67:
68: Try {
69: $Excel = New-Object -Com Excel.Application
70: #$Excel.visible = $True
71: $Excel = $Excel.Workbooks.Add()
72: $NameFile = " HealthCheck_$(get-date -format 'yyyy_MM_dd')"
73: $NameFileTest = "$($NameFile).*"
74: $path = Join-Path -Path $($ReportOn) -ChildPath $NameFile
75: $pathTest = Join-Path -Path $($ReportOn) -ChildPath $NameFileTest
76:
77: if (Test-Path $pathTest -PathType Any) {
78: try {
79: Remove-Item -Path $pathTest -Force
80: } catch {
81: Show-MessageBox -msg "The file $($NameFile) is using by another process" -title 'Error '
82: Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)
83: break
84: }
85: }
86:
87: $Excel.saveas($path)
88:
89: $HeaderHash = @{}
90: $Itens = 1
91: $Files = Get-childitem $ReportOn -Filter "*.sql"
92:
93:
94: $Files | % {
95: $NumberOfWorkSheets = $Files.count
96:
97:
98: $SqlQuery = Get-Content $_.fullname
99: $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
100: $SqlCmd.CommandText = $SqlQuery
101: $SqlCmd.Connection = $SqlConnection
102: $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
103: $SqlAdapter.SelectCommand = $SqlCmd
104: $DataSet = New-Object System.Data.DataSet
105:
106: $SqlAdapter.Fill($DataSet) | Out-Null
107:
108: $DataExcel = $DataSet.Tables[0] | select * -ExcludeProperty 'RowError','RowState','Table','ItemArray','HasErrors'
109:
110: for ( $i = $Excel.Worksheets.count ;$i -lt $NumberOfWorkSheets; $i++) {
111: $Excel.Worksheets.add()
112: }
113: Write-Host "Processing File $($_.Name)"
114: $Name = "$($_.name) "
115: $Excel.Worksheets.Item($Itens).name = $Name.substring(0,30)
116:
117: $Sheet = $Excel.Worksheets.Item($Itens)
118: $property=@()
119:
120: For ( $i = 0 ;$i -lt $DataSet.Tables[0].columns.count ; $i++) {
121: $property += @($DataSet.Tables[0].columns[$i].columnname)
122: }
123:
124: $Column = 1
125: $Row = 1
126: foreach ($header in $property) {
127: $HeaderHash[$header] = $Column
128: $Sheet.Cells.Item($Row,$Column) = $header.toupper()
129: $Column ++
130: }
131:
132: $WorkBook = $Sheet.UsedRange
133: $WorkBook.Interior.ColorIndex = 19
134: $WorkBook.Font.ColorIndex = 11
135: $WorkBook.Font.Bold = $True
136: $WorkBook.HorizontalAlignment = -4108
137:
138: $Row ++
139: $DataExcel | % {
140: foreach ($header in $property) {
141: if ($thisColumn = $HeaderHash[$header]) {
142: $Sheet.Cells.Item($Row,$thisColumn) = [string]$_.$header
143: }
144: }
145: $Row ++
146: }
147: $Excel.save()
148: $Itens ++
149: }
150: } catch {
151: Write-ScriptLog $("$(get-date -format yyyy/MM/dd) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])") -date $($date) -reporton $($ReportOn)
152: } Finally {
153: $SqlConnection.Close()
154: $Excel.Close()
155: Write-Host "Finished"
156: Show-MessageBox -msg "Finished !!! "
157: }
158:
159:
160: }
161:
162:
163: #endregion Script
164:
165: Gather-Result
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)
Show-MessageBox
Show-InformationBox
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).
1: [Parameter(Position=0, Mandatory=$true)] [String]$Server,
2: [Parameter(Position=1, Mandatory=$true)] [String]$UserName,
3: [Parameter(Position=2, Mandatory=$true)] [String]$Password,
4: [Parameter(Position=2, Mandatory=$true)] [String]$Database
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
1: Get-Process "Excel*" | Stop-Process
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.
1: #region Functions
2:
3:
4: function Write-ScriptLog
5: {
6: param($msg,$date,$ReportOn)
7: if ($ReportOn -ne "")
8: { Add-Content -Path (Join-Path $ReportOn "SQLDmRepository_$($ServerName)_$($date).log") -Value $msg}
9: Write-Output $msg
10: }
11:
12: function Show-MessageBox ($title, $msg) {
13: [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
14: [Windows.Forms.MessageBox]::Show($msg, $title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Warning, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null
15: }
16:
17: function Show-InformationBox ([string]$Title,[string]$Message) {
18: [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
19: [Windows.Forms.MessageBox]::Show($Message, $Title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Information, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null
20: }
21:
22:
23:
24: #endregion functions
25:
26: #region Script
27: Function Gather-Result {
28: param(
29: [Parameter(Position=0, Mandatory=$true)] [String]$Server,
30: [Parameter(Position=1, Mandatory=$true)] [String]$UserName,
31: [Parameter(Position=2, Mandatory=$true)] [String]$Password,
32: [Parameter(Position=3, Mandatory=$true)] [String]$Database
33: )
34:
35:
36: Get-Process "Excel*" | Stop-Process
37: # $Server = '.'
38: # $UserName = 'xx'
39: # $Password = 'xxxxx'
40: #
41: # #Variables
42: # $Database = "Database"
43:
44: #Variable to Change The Folder ##################################################################
45: $ReportOn = "c:\HealthCheck"
46: #Variable to Change The Folder ##################################################################
47:
48:
49: $ConnectionFailed = $false
50: $Date = get-date -format 'yyyy_MM_dd'
51:
52: #test path . If does not exist break the code
53: if (-not (Test-Path -Path $reporton -PathType Container)) {
54: Show-MessageBox -msg "Path $($ReportOn) does not exist" -title 'Error'
55: break
56: }
57:
58: try {
59: #Connect to SQL Server using .net
60: $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
61: $SqlConnection.ConnectionString = "Server = $($Server); Database = $($Database); User = $($Username); PWD = $($Password)"
62: $SqlConnection.Open()
63: } catch {
64: Show-MessageBox -msg "Error to connect to Server $($Server) Additional information at log file" -title 'Error Connection'
65: Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)
66: break
67: }
68:
69: Try {
70: $Excel = New-Object -Com Excel.Application
71: #$Excel.visible = $True
72: $Excel = $Excel.Workbooks.Add()
73: $NameFile = " HealthCheck_$(get-date -format 'yyyy_MM_dd')"
74: $NameFileTest = "$($NameFile).*"
75: $path = Join-Path -Path $($ReportOn) -ChildPath $NameFile
76: $pathTest = Join-Path -Path $($ReportOn) -ChildPath $NameFileTest
77:
78: if (Test-Path $pathTest -PathType Any) {
79: try {
80: Remove-Item -Path $pathTest -Force
81: } catch {
82: Show-MessageBox -msg "The file $($NameFile) is using by another process" -title 'Error '
83: Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)
84: break
85: }
86: }
87:
88: $Excel.saveas($path)
89:
90: $HeaderHash = @{}
91: $Itens = 1
92: $Files = Get-childitem $ReportOn -Filter "*.sql"
93:
94:
95: $Files | % {
96: $NumberOfWorkSheets = $Files.count
97:
98:
99: $SqlQuery = Get-Content $_.fullname
100: $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
101: $SqlCmd.CommandText = $SqlQuery
102: $SqlCmd.Connection = $SqlConnection
103: $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
104: $SqlAdapter.SelectCommand = $SqlCmd
105: $DataSet = New-Object System.Data.DataSet
106:
107: $SqlAdapter.Fill($DataSet) | Out-Null
108:
109: $DataExcel = $DataSet.Tables[0] | select * -ExcludeProperty 'RowError','RowState','Table','ItemArray','HasErrors'
110:
111: for ( $i = $Excel.Worksheets.count ;$i -lt $NumberOfWorkSheets; $i++) {
112: $Excel.Worksheets.add()
113: }
114: Write-Host "Processing File $($_.Name)"
115: $Name = "$($_.name) "
116: $Excel.Worksheets.Item($Itens).name = $Name.substring(0,30)
117:
118: $Sheet = $Excel.Worksheets.Item($Itens)
119: $property=@()
120:
121: For ( $i = 0 ;$i -lt $DataSet.Tables[0].columns.count ; $i++) {
122: $property += @($DataSet.Tables[0].columns[$i].columnname)
123: }
124:
125: $Column = 1
126: $Row = 1
127: foreach ($header in $property) {
128: $HeaderHash[$header] = $Column
129: $Sheet.Cells.Item($Row,$Column) = $header.toupper()
130: $Column ++
131: }
132:
133: $WorkBook = $Sheet.UsedRange
134: $WorkBook.Interior.ColorIndex = 19
135: $WorkBook.Font.ColorIndex = 11
136: $WorkBook.Font.Bold = $True
137: $WorkBook.HorizontalAlignment = -4108
138:
139: $Row ++
140: $DataExcel | % {
141: foreach ($header in $property) {
142: if ($thisColumn = $HeaderHash[$header]) {
143: $Sheet.Cells.Item($Row,$thisColumn) = [string]$_.$header
144: }
145: }
146: $Row ++
147: }
148: $Excel.save()
149: $Itens ++
150: }
151: } catch {
152: Write-ScriptLog $("$(get-date -format yyyy/MM/dd) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])") -date $($date) -reporton $($ReportOn)
153: } Finally {
154: $SqlConnection.Close()
155: $Excel.Close()
156: Write-Host "Finished"
157: Show-MessageBox -msg "Finished !!! " -title "Information"
158: }
159:
160:
161: }
162:
163:
164: #endregion Script
165:
166: Gather-Result
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.
The excel code is a cutomized version of the OUT-EXCEL from Pathological Scripter
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 :
RedGate – SQL Server DMV starter pack
Download The Code and/or Video
#PowerShellLIfeStyle
