Running PowerShell scripts from SSMS Query Editor


Hi guys, this tip is simple. Did you know that you can run your PowerShell scripts, cmdlets, functions from the SSMS Query Editor ?

yeahhh..using xp_cmdshell Sorriso. Just remember two things:

1 – Use Results to  text

2 – The context of the computer that will run the PowerShell is the Query Editor SQL Server connection. So if you are connected in the Server2 and running  PowerShell functions, be sure that this functions are in your profile in Server2

 

1 – Disk Space :

Using  Get-Diskspace function (@sqlvariant)  Locally :

   1: xp_cmdshell 'powershell.exe -command "get-diskspace ."'

image

 

Remoting :

   1: xp_cmdshell 'powershell.exe -command "get-diskspace -servername ObiWan"'

image

 

All Servers in Servers.txt

   1: xp_cmdshell 'powershell.exe -command "get-diskspace -servername (get-content c:\temp\servers.txt)"'

image

 

You can also use get-counter and \LogicalDisk(*)\% Free Space to get all counter instances :

   1: xp_cmdshell 'powershell.exe -noprofile -command "Get-counter -counter ''\LogicalDisk(*)\% Free Space'' | select -expand countersamples"'

image

 

Remoting :

   1: xp_cmdshell 'powershell.exe -noprofile -command "Get-counter -computername ObiWan -counter ''\LogicalDisk(*)\% Free Space'' | select -expand countersamples"'

image

 

2 –  Services :

   1: xp_cmdshell 'powershell.exe -noprofile -command "Get-Service"'

image

 

Only Services Stopped :

   1: xp_cmdshell 'powershell.exe -noprofile -command "Get-Service |  where {$_.status -eq ''Stopped''}"'

image

 

Only SQL Server Services stopped :

   1: xp_cmdshell 'powershell.exe -noprofile -command "Get-Service -name *sql* |  where {$_.status -eq ''Stopped''}"'

image

 

Remoting :

   1: xp_cmdshell 'powershell.exe -noprofile -command "Get-Service -computername ObiWan -name *sql* |  where {$_.status -eq ''Stopped''}"'

image

 

Yeahhh !!!!!!

As today is the Rock  International Day, I will quote Joan Jett  I Love Rock ‘N’ Roll : (with a bit change)

I love rock n’ roll (and PowerShell)
So put another dime in the jukebox, baby
I love rock n’ roll (and PowerShell)
So come and take your time and dance with me

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s