“Every morning in Africa, a Gazelle wakes up. It knows it must run
faster than the fastest lion or it will be killed. Every morning a Lion
wakes up. It knows it must outrun the slowest Gazelle or it will starve
to death. It doesn’t matter whether you are a Lion or a Gazelle.……….
when the sun comes up, you’d better be running.”
Unknown Author
Guys, quick and simple-post. My good friend Edvaldo (@edvaldocastro02), or Socorro´s Husband , he needed a quick and simple way to gather and store the disk information from his servers on a table in a central repository. For security reasons of his company, it can not do this from his machine. Then we put the script on each server, running with a SQL Server Agent and saving on his machine.
The Script is simple. First we need to download the Aaron Nelson´s (@sqlvariant) Get-DisksSpace , Chad Miller´s (@cmille19) Out-DataTable and Write-DataTable.
Then it is just add this function in your PowerShell module, in my case functions.psm1, and import it in the profile.
Lets create the table in the PoshTest Database :
1: USE [PoshTest]
2: GO
3:
4: /****** Object: Table [dbo].[tbl_DiskSpace] Script Date: 25/05/2012 22:03:53 ******/
5: SET ANSI_NULLS ON
6: GO
7:
8: SET QUOTED_IDENTIFIER ON
9: GO
10:
11: SET ANSI_PADDING ON
12: GO
13:
14: CREATE TABLE [dbo].[tbl_DiskSpace](
15: [Date] [smalldatetime] NULL,
16: [SystemName] [varchar](50) NULL,
17: [Name] [varchar](50) NULL,
18: [SizeInGB] [varchar](50) NULL,
19: [FreeInGB] [varchar](50) NULL,
20: [PercentFree] [varchar](50) NULL,
21: [Label] [varchar](50) NULL
22: ) ON [PRIMARY]
23:
24: GO
25:
26: SET ANSI_PADDING OFF
27: GO
Now it is time to write the script :
1: param ($ServerName = “.”)
2: $variable = ( Get-DisksSpace $ServerName | select @{Expression= {(Get-Date -Format "yyyy-MM-dd hh:mm:ss") };Label = "Date"},SystemName,Name,SizeInGB,FreeInGB,PercentFree,Label)
3: $valuedatatable = Out-DataTable -InputObject $variable
4: Write-DataTable -ServerInstance YourComputer -Database PoshTest -TableName tbl_DiskSpace -Data $valuedatatable
Save in some folder, in my case c:\posh\diskspace.ps1
Now It is just create the SQL Server Agent job. If you are using SQL Server 2012 you can do it with PowerShell Job type, otherwise should be CMDExec Type. For us is CMD.
1: PowerShell.exe "c:\posh\diskspace.ps1 ."
and schedule it 
Simple, Clean and Fast. Classic PowerShell
#PowerShellLifeStyle
Hope you guys like it. As PowerShell, those songs makes my brain jump out of my head .
YEAHHHH !!!!!!!!
Hollywood Undead “Hear Me Now”
4 – Nathaniel Dawkins – Bring It On
5 – Breaking Benjamin – The Diary of Jane
7 – Hollywood Undead Lights out
7 - Hollywood Undead – Comin’ in Hot
8 – P.O.D – Youth Of The Nation
10 – Roy Jones – Can’t be touched
#PowerShelhLifeStyle
Pessoal, o grupo SQLSouthWest da Inglaterra do grande amigo e Jedi Jonathan Allen (@fatherjack) esta proporcionando alguns treinamentos online (live meeting) com os Jedis do SQL Server Mundial como Mladen Prajdic (@mladenprajdic), Aaron Nelson(@sqlvariant)..
É uma otima oportunidade de aprender com os melhores do mundo !!!!
Segue o link do evento
e aqui todos os livemeetings agendados
Não perca..Eu vou estar lá aprendendo !!!!
Abs !!!
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
Performing Dangerous Operations in SQL Server
 |
“With great power comes great responsibility…and the –whatif to help” Ben Parker…and Laerte Junior |
Fellows, we know that not exists small “messed“ in the DBA job. When it happens, always is in HUGE proportions.
Fortunately we have in the advanced functions a feature that we can implement .
The -whatif parameter which is nothing more than it will show to you what it will do without run the command that you are sending. It is something like : “What am I about to do?” unless “Holy All Saints. Do I have a backup ?”
To implement this feature we need to set the SupportsShouldProcess argument in the [CmdletBinding()] . It is part of the Advanced functions confirmation methods and you can get a complete help typing help about_functions_cmdletbinding.
In it´s definition :
“When the SupportsShouldProcess argument is set to true, it indicates that
the function supports calls to the ShouldProcess method, which is used to
prompt the user for feedback before the function makes a change to the
system. When this argument is specified, the Confirm and WhatIf
parameters are enabled for the function”
Let’s write a function to drop objects. It is a VERY dangerous operation, so it is better we implement a –whatif.
I created two functions to show . Get-MSSQLTable and GET-MSSQLProcedure. Why ?
Because the word is reusable functions,if fact this is part of my new Simple-Talk article and I hope you liked it. In this article I will show my technique to create solutions using PowerShell and reusable functions is the main technique.
I can say that is the same that we use in SQLPSX because I just learned with a member in the PowerShell Jedi Council , Master Chad Miller .
So, to a better explanation we have 2 functions that returns 2 smo objects (table and Stored procedure) . Then we will pass by pipeline to the Drop-SQLObject.
See, it is not a Drop-SQLTable or Drop-SQLProcedure. It It is function that will perform a drop in any object that support this method.
[\Updated - Check if the object supports Drop method]
How can my function check if it is a Object that supports drop method ?
Just add
1: ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})
in the ValidateScript parameter set :
1:
2: param(
3: [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]
4: [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject,
5: [switch] $Force
6: )
Again, thanks to Shay Levy to send me this tip 
[Updated\]
My first step to this function , say Drop-SQLObject was to create it receiving by parameter a SMO object and dropping inside a process block, but I faced a problem with Foreach enumerations in pipeline , and unfortunatly to retrive the Database Objects (tables, stored procedures and so on) in the SMO you will need to use enumerators:
1: function Drop-SQLObject_1
2: {
3: [CmdletBinding()]
4: param(
5: [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]
6: [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*"})] $SmoObject
7: )
8:
9: process {
10: $SmoObject.drop()
11: }
12:
13: }
As both get-msssqltable and get-mssqlstoredprocedure use a foreach enumeration to tables, a common issue happens trying to drop the object.
Basically Enumerators can be used to read the data in the collection, but they cannot be used to modify the underlying collection.
Then an error showed to me :
An error occurred while enumerating through a collection: Collection was modified; enumeration operation may not execute..
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\sqlserver\SQLServer.psm1:1233 char:16
+ foreach <<<< ($table in $tables)
+ CategoryInfo : InvalidOperation: (Microsoft.SqlSe…dListEnumerator:SmoSortedListEnumerator) [], RuntimeException
+ FullyQualifiedErrorId : BadEnumeration
To solve this problem I used an array (Idea that I found in a forum that Jedi Boe Prox – @proxb solved) and begin/process/end block.
The problem was that when the execution back to the get-sqltable process, the enumerator was trying to do a modification in the collection.
Then was just load the enitire object in process block in a array $MyObject
1: foreach ($drop in $SmoObject) {
2: $MyObject += $drop
3: Write-Host "Droping $($SmoObject.GetType().name) - $($SmoObject)"
4: }
and effectively drop in the end block at the drop-sqlobject with a simple for in this array.
1: $count = $MyObject.count
2: for ($i = 0; $i -lt $count; $i++) {
3: $MyObject[$i].Drop()
4:
5: }
after all the items be processed (end block) .
I don’t know it is the better solution to solve this limitation, but It works.
1: function Drop-SQLObject
2: {
3: [CmdletBinding()]
4: param(
5: [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]
6: [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*"})] $SmoObject
7: )
8:
9: begin {
10: $MyObject = @()
11: }
12:
13: process {
14: foreach ($drop in $SmoObject) {
15: $MyObject += $drop
16: Write-Host "Deleting $($MyObject[$i].name)"
17: }
18: }
19: end {
20: $count = $MyObject.count
21: for ($i = 0; $i -lt $count; $i++) {
22: $MyObject[$i].Drop()
23:
24: }
25: }
26:
27: }
28:
Now I can use –whatif
1:
2: function Drop-SQLObject
3: {
4: [CmdletBinding(SupportsShouldProcess=$true)]
5: param(
6: [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]
7: [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*"})] $SmoObject
8:
9: )
10:
11: begin {
12: $MyObject = @()
13: }
14:
15: process {
16: if ( $PSCmdlet.ShouldProcess("$($SmoObject.GetType().name) - $($SmoObject)") ) {
17:
18: foreach ($drop in $SmoObject) {
19: $MyObject += $drop
20: Write-Host "Droping $($SmoObject.GetType().name) - $($SmoObject)"
21: }
22: }
23: }
24: end {
25: $count = $MyObject.count
26: for ($i = 0; $i -lt $count; $i++) {
27: $MyObject[$i].Drop()
28:
29:
30: }
31: }
32:
33: }
You see that in the [CmdletBinding(SupportsShouldProcess=$true)] I am “enabling” whatif (it also enable the ConfirmImpact Argument – There is a example below in [UPDATED])and the if ( $PSCmdlet.ShouldProcess(“$($SmoObject.GetType().name) – $($SmoObject)”) ) is the condition to NOT whatif, or TO perform the operation.
What you put in the $pscmdlet.ShouldProcess quotas is what will be displayed in the screen.
In my case I am showing the type of the object with the method gettype (if it is a table, stored procedure..etc) and the object itself – schema and name)
Then if I use :
1: Get-MSSQLTable -Server "YourServer" -Database "test" -TableName "Test*" | Drop-SQLObject -whatif
Will display :
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste1]“.
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste2]“.
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste3]“.
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste4]“.
And will not drop the table. The function is just showing to you what it will do
But if I change omitting whatif
1: Get-MSSQLTable -Server "YourServer" -Database "test" -TableName "Test*" | Drop-SQLObject
Droping Table – [dbo].[teste1]
Droping Table – [dbo].[teste2]
Droping Table – [dbo].[teste3]
Droping Table – [dbo].[teste4]
Dropped !!!!!
As you can see the drop-sqlobject is receiving by pipeline any SMO object, then you can use it any database object that has the drop method of course. Stored Procedures, Views, Functions, Database..etc
Try in your sample database to perform a whatif in all stored procedures that start with “sp_”
1: Get-MSSQLStoredProcedure -Server "YourServer" -Database "test" -ProcedureName "sp_*" | Drop-SQLObject -whatif
Complete code ——————————————————————————————-
1: function Get-MSSQLTable {
2:
3: [CmdletBinding()]
4: param(
5: [Parameter(Position=0, Mandatory=$true)] [String]$Server,
6: [Parameter(Position=1, Mandatory=$true)] [String]$Database,
7: [Parameter(Position=2, Mandatory=$false)] [String]$TableName
8: )
9:
10: begin {
11:
12: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
13: $ServerName=New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
14:
15: }
16:
17:
18: process {
19: $ServerName.Databases | where {$_.name -eq "$Database" } | % {
20: foreach ($table in $_.tables) {
21:
22: if ($tableName) {
23: $tables = $table | where {$_.name -like "$tablename*"}
24: } else {
25: $tables = $table
26: }
27:
28: Write-Output $tables
29:
30: }
31:
32: }
33:
34: }
35:
36: }
37:
38: function Get-MSSQLStoredProcedure {
39:
40: [CmdletBinding()]
41: param(
42: [Parameter(Position=0, Mandatory=$true)] [String]$Server,
43: [Parameter(Position=1, Mandatory=$true)] [String]$Database,
44: [Parameter(Position=2, Mandatory=$false)] [String]$ProcedureName
45: )
46:
47: begin {
48:
49: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
50: $ServerName=New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
51:
52: }
53:
54:
55: process {
56: $ServerName.Databases | where {$_.name -eq "$Database" } | % {
57: foreach ($Proc in $_.storedprocedures) {
58:
59: if ($ProcedureName) {
60: $procs = $Proc | where {$_.name -like "$ProcedureName*"}
61: } else {
62: $procs = $proc
63: }
64:
65: Write-Output $procs
66:
67: }
68:
69: }
70:
71: }
72:
73: }
74:
75:
76:
77:
78: function Drop-SQLObject
79: {
80: [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='High')]
81: param(
82: [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]
83: [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject,
84: [switch] $Force
85: )
86:
87: begin {
88: $MyObject = @()
89: }
90:
91: process {
92: if ( $force -or $PSCmdlet.ShouldProcess("$($SmoObject.GetType().name) - $($SmoObject)") ) {
93:
94: foreach ($drop in $SmoObject) {
95: $MyObject += $drop
96: Write-Host "Droping $($SmoObject.GetType().name) - $($SmoObject)"
97: }
98: }
99: }
100: end {
101: $count = $MyObject.count
102: for ($i = 0; $i -lt $count; $i++) {
103: $MyObject[$i].Drop()
104:
105:
106: }
107: }
108:
109: }
110: #
111: Get-MSSQLTable -Server "xx" -Database "teste" -TableName "Test*"
112: #
113: Get-MSSQLTable -Server "xx" -Database "teste" -TableName "Test*" | Drop-SQLObject -whatif
114: Get-MSSQLTable -Server "xx" -Database "teste" -TableName "Test*" | Drop-SQLObject -Force
115: #
116: Get-MSSQLStoredProcedure -Server "xx" -Database "teste" -ProcedureName "test*" | Drop-SQLObject -whatif
117: Get-MSSQLStoredProcedure -Server "xx" -Database "teste" -ProcedureName "test*" | Drop-SQLObject
Now I can say that I am feeling the Power of the PowerShell.
[Updated]
Another member in the PowerShell Jedi Council, Master Shay Levy, (@shaylevy) had a good idea. Why not add auto confirmation wich you can override using –force switch ?
Just add ConfirmImpact argument in the cmdletbinding line :
1: [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='High')]
and add $force in the conditions :
1: if ( $force -or $PSCmdlet.ShouldProcess("$($SmoObject.GetType().name) - $($SmoObject)") ) {
2:
3: and
4:
5: if ($force -or (-not $whatif) ) {
This way, to each drop will show to you a window to confirm the operation :
Haa Laerte, but I need to drop tons of objects,to each one a windows will show ?
Or you select the option Yes to All in the window displayed,
or Just pass –force parameter in the cmdlet call
1: Get-MSSQLTable -Server "7-pc" -Database "teste" -TableName "Test*" | Drop-SQLObject -Force
BIG THANKS SHAY !!!!!!!!!
 |
This example , and all the others that will be in my new S-T article, are basically one of my session abstract that I submitted to PASS SUMMIT 2012 and you can see my abstracts in here.
If, by some gift of the universe, I have the chance and honor to be chosen, I will show to you the entire article in practice. No slides, just pure PowerShell Code in our heads.
Resources :
Sean Kearney (@energizedtech)
Implementing the–WHATIF into an Advanced Function in Windows Powershell
Chad Miller – (@cmille19)
Sev17 – SQL Server, PowerShell and so on
Codeplex – SQLPSX (SQL Server PowerShell Extensions)
#PowerShellLifeStyle
Após um bom tempo sem blogar, meu primeiro post não será técnico. Não falarei sobre PowerShell ou SQL Server.
Na verdade este post eu dedico aos meu amigos Edvaldo (@edvaldocastro02) e Socorro (@maryhelpa), vulgos EdvaldNeguer e Socorrin dos Teclados (kakaka, perco o amigo mas não perco a piada). ELES VÃO SE CASARRRRRRRRRRRRRR !!!
Para Provar que é VERDADE !!!
Acho que eu fui um dos poucos a saber quando eles começaram a namorar e novamente tive o prazer de ser um dos poucos a saber quando decidiram se casar. Esse tipo de confiança não tem preço.
Eu felizmente sou um cara que posso contar nos dedos os AMIGOS que tenho , mas estes, realmente o são. Sempre tiveo carinho e afeição do Edis (me chamando de tio o FDP) e a Socorro. Conheço a história de luta e superação da Socorro e sei que ela merecia uma boa pessoa como o Edvaldo. Um cara voltado pra família.
Um cara do bem !!!
São nas pequenas coisas que a gente percebe isso. Esses tempos, após alguns meses em tratamento de saude, eu disse ao Edis : “Cara, voltei. vou procurar emprego” e ele “Manolo, caras como você não procuram, são procurados” 
Eu espero que ele tenha dito isso não pela Polícia (hahahahahaha)
Brincadeira a parte, isso me fez um bem danado !!! Obrigado pelas palavras de carinho meu velho !!!!
Bom, meninos, a vocês toda a felicidade do mundo, que vocês tenham tudo que desejam na vida e que o nosso #sqlbaby venha logo.
Quanto aos presentes, pessoal que for, não deêm coisas do genêro : Livro do SQL Server 2012, 2 trilogias Star Wars…etc. Não queremos acabar com a lua de mél deles né. Lembrem-se são dois dbas nerds que irão se casar…,então (kakakakak)
Meus 2 cents a esta união não poderia ser de outra maneira :
Get-Socorro | Get-Edvaldo | Invoke-Casamento | foreach {
Get-Happiness | Out-File c:\FolderDaVida\FelizesParaSempre.txt
# (loop eterno)
}
é simples, mas é do coração. (PS – EU VOU ESTAR LÁ) e tocarei a Marcha Imperial quando o Edis entrar no altar (hahahahahahah) #interna
Até agosto meus amigos !!!! E como um bom nerd :
We have a server of approval, yes I know it’s approval, but because of holidays and how we are a major retailer, this server has become critical to the business, given that many changes are made to the systems at this time.
As our monitoring process is a bit bureaucratic to happen, we needed a quick solution for verification of low disk space on this server. Solution. Powershell and WMI
I wanted to build a more flexible solution possible, so I thought of a way to the script get all the server volumes and report on a value below X (passed parameter). My first version was:
1: $Hostname = 'Server01'
2: $Space = 2048
3: $SpaceGB = $Space / 1024
4: Get-WmiObject -computername $Hostname `
5: -query " select Name from Win32_Volume where DriveType = 3 and SystemVolume = false and FileSystem = 'NTFS'" | foreach {
6:
7: try {
8:
9: $Volume = ($($_.name).TrimEnd("\")).replace("\" ,"\\")
10: $SourceIdentifier = (($($_.name)).replace("\" ,"")).replace(":","")
11: $query = "Select * from __instanceModificationEvent WITHIN 5 WHERE TargetInstance ISA 'Win32_PerfFormattedData_PerfDisk_LogicalDisk' and targetinstance.Name=`'$($Volume)`' AND TargetInstance.freemegabytes < $space"
12: Register-WMIEvent -ComputerName $Hostname `
13: -Query $Query `
14: -SourceIdentifier "LowDiskSpace$($SourceIdentifier)" `
15: -Action {
16: $Gb = "{0:n2}" -f ($event.SourceEventArgs.NewEvent.targetinstance.FreeMegabytes/1024);
17: Send-MailMessage -SmtpServer XXX.X.XX.XXX -From 'SQLExecutive@contoso.com' -To 'laerte.junior@contoso.com.br' -Subject "Espaco em Disco Abaixo de $($SpaceGB) GB - Servidor $($event.SourceEventArgs.NewEvent.targetinstance.__SERVER)" -Body " Volume $($event.SourceEventArgs.NewEvent.targetinstance.name) com $Gb GB";
18: Write-EventLog -LogName MonitorLowDiskSpace -EventId 01 -Message "Espaco em Disco Abaixo de $($SpaceGB) GB - Servidor $($event.SourceEventArgs.NewEvent.targetinstance.__SERVER) Volume $($event.SourceEventArgs.NewEvent.targetinstance.name) com $Gb GB" -Source Poshevent -EntryType Warning -Category 1 -ComputerName $Hostname
19:
20: } | Out-Null
21: $log = "Event registered in Volume $($Volume) computer $($Hostname)"
22:
23: } catch {
24: $Log = "Failed to Register WMI Event on {0} volume {2}. {1}" -f $($Hostname),$($Volume),$($_.Exception.Message)
25: }
26: $log | Out-File "$Env:TEMP\MsgEvents$(Get-date -format 'ddMMyyyy').log" -Append
27: }
28: Send-MailMessage -SmtpServer XXX.X.XX.XXX -From 'SQLExecutive@contoso.com' -To 'laerte.junior@contoso.com.br' -Subject "Eventos de Espaco em Disco" -Body "Script de Registro de evento em espaco em disco executado no servidor $($Hostname). Verificar Arquivo de log $Env:TEMP\MsgEvents$(Get-date -format 'ddMMyyyy').log ";
29:
30:
Lets see some things :
First I need to find the volumes, that is because I am using Win32_Volume and properties DriveType = 3 and SystemVolume = false and FileSystem = ‘NTFS’.
The drive type = 1 show only Local Disk, System Volume = false to not use system volume and FileSystem = ‘NTFS’ only want the NTFS formatted volumes.
Then I am using Win32_PerfFormattedData_PerfDisk_LogicalDisk class to get the data from this volumes .
in this line
$Volume = ($($_.name).TrimEnd(“\”)).replace(“\” ,”\\”)
I formated the volume to take off the ‘\’ at the end of the volume, because the mount points is showed as d:\something\ and I change the ‘\’ to ‘\\’ because ‘\’ is a special char.
in this line
$SourceIdentifier = (($($_.name)).replace(“\” ,”")).replace(“:”,”")
I cleaned the volume name to concatenate in –SourceIdentifier “LowDiskSpace$($SourceIdentifier)”. This way I haveJob Name each event registered
Then I createed a Log File with messsage successful or failed and put at $env:temp
$log | Out-File “$Env:TEMP\MsgEvents$(Get-date -format ‘ddMMyyyy’).log” -Append
This way I do not have a email to each event registered or not, juts one email to see the log file at $Env:TEMP\MsgEvents$(Get-date -format ‘ddMMyyyy’).log
Also Created a new Entry in EventViewer called MonitorLowDiskSpace to log the data into EventViewer too.
New-EventLog MonitorLowDiskSpace -source PoshEvent
And was working fine, but in the first problem with disk space a message each 5 seconds was delivery to my email. (within clause to 5)
Then, I changed to 30 minutes (1800 seconds). But, other problem. when the low disk space happened again, the first email only was delivery after 30 minutes. Bummer.
So, I decided to create my own alert suppression.
Handle with alert Suppression
My Idea is simple. Just create a log file with the date and hour that the email was sent and then in the next execution I test if the minutes between current date (get-date) and the data logged into the file is greater than X minutes. (Pareameter $Time). If it is, send email again and update the file with the current date time. No, just ignore it.
So the code is :
1: #New-EventLog MonitorLowDiskSpace -source PoshEvent
2: Param (
3: [ValidateNotNullOrEmpty()] $Hostname,
4: [ValidateNotNullOrEmpty()] $Space
5: [ValidateNotNullOrEmpty()] $Time
6:
7: )
8: #$Hostname = 'Server01'
9: #$Space = 2048
10: #Time 10 -minutes
11: $SpaceGB = $Space / 1024
12: Get-WmiObject -computername $Hostname `
13: -query " select Name from Win32_Volume where DriveType = 3 and SystemVolume = false and FileSystem = 'NTFS'" | foreach {
14:
15: try {
16:
17: $Volume = ($($_.name).TrimEnd("\")).replace("\" ,"\\")
18: $SourceIdentifier = (($($_.name)).replace("\" ,"")).replace(":","")
19: $SourceIdentifier = "LowDiskSpace$($SourceIdentifier)"
20: $FileName = "$($Env:TEMP)\$($SourceIdentifier).log"
21: $pso = new-object psobject -property @{FileName=$FileName;Time=$Time}
22:
23: $query = "Select * from __instanceModificationEvent WITHIN 5 WHERE TargetInstance ISA 'Win32_PerfFormattedData_PerfDisk_LogicalDisk' and targetinstance.Name=`'$($Volume)`' AND TargetInstance.freemegabytes < $space"
24: Register-WMIEvent -ComputerName $Hostname `
25: -Query $Query `
26: -SourceIdentifier $SourceIdentifier `
27: -Action {
28: $SendEmail = $true
29: if (Test-Path $event.MessageData.FileName -PathType Leaf ) {
30: $SendEmail = ((New-TimeSpan -Start ([datetime]::parseexact((get-content $event.MessageData.FileName),'yyyy-MM-dd HH:mm:ss',$null)) -End (get-date -format "yyyy-MM-dd HH:mm:ss")).totalminutes -ge $event.MessageData.Time) ;
31: }
32:
33: if ($SendEmail) {
34: $Gb = "{0:n2}" -f ($event.SourceEventArgs.NewEvent.targetinstance.FreeMegabytes/1024);
35: Send-MailMessage -SmtpServer XXX.X.XX.XXX -From 'SQLExecutive@contoso.com' -To 'laerte.junior@contoso.com.br' -Subject "Espaco em Disco Abaixo de $($SpaceGB) GB - Servidor $($event.SourceEventArgs.NewEvent.targetinstance.__SERVER)" -Body " Volume $($event.SourceEventArgs.NewEvent.targetinstance.name) com $Gb GB";
36: Write-EventLog -LogName MonitorLowDiskSpace -EventId 01 -Message "Espaco em Disco Abaixo de $($SpaceGB) GB - Servidor $($event.SourceEventArgs.NewEvent.targetinstance.__SERVER) Volume $($event.SourceEventArgs.NewEvent.targetinstance.name) com $Gb GB" -Source Poshevent -EntryType Warning -Category 1 -ComputerName $Hostname
37: get-date -format 'yyyy-MM-dd HH:mm:ss' | Out-File $event.MessageData.FileName -Force
38: }
39:
40: } | Out-Null
41: $log = "Event registered in Volume $($Volume) computer $($Hostname)"
42:
43: } catch {
44: $Log = "Failed to Register WMI Event on {0} volume {2}. {1}" -f $($Hostname),$($Volume),$($_.Exception.Message)
45: }
46: $log | Out-File "$Env:TEMP\MsgEvents$(Get-date -format 'ddMMyyyy').log" -Append
47: }
48: Send-MailMessage -SmtpServer XXX.X.XX.XXX -From 'SQLExecutive@contoso.com' -To 'laerte.junior@contoso.com.br' -Subject "Eventos de Espaco em Disco" -Body "Script de Registro de evento em espaco em disco executado no servidor $($Hostname). Verificar Arquivo de log $Env:TEMP\MsgEvents$(Get-date -format 'ddMMyyyy').log ";
49:
50:
Big Thanks to Shay Levy to help in my datetime questions 
#PowerShellLifeStyle
Pessoal, segue os scripts e slides da minha sessão
Hey PowerShell , você pode me notificar ?
Eu coloquei meu modulo de funções também. Basta voce colocar no seu profile e pronto. No caso da send-smtpemail, basta trocar dentro dela o usuario e a senha em :
#sua credencial aqui
$mailer.Credentials = New-Object System.Net.NetworkCredential(“Nome“, “senha“); # Put username without the @GMAIL.com or – @gmail.com
Abs
#PowerShellLifeStyle
Nos dias 19 e 20 de outubro acontecerá o maior evento gratuito online da América Latina. O 24 hours of PASS LATAM , o mesmo contará com uma grade cheia de novidades sobre o SQL Server “Denali” (versão CTP3). Haverá diversos palestrantes desde Espanha, Costa Rica, Venezuela, Guatemala, Argentina, Brasil e etc.
Eu vou falar dia 19 as 11:00 da manhã (Brasilia Time) 09:00 CDT (Mexico), sobre notificações de eventos usando PowerShell e WMI/WMI Server Events. Veremos o que é WMI , WQL, Event Query Language, quais suas variações e como usar. Depois será codigo, codigo, codigo…
Inscrições:24 Horas PASS LATAM – Denali “Camino al 2012″
Grade do Evento e Fonte :
