T-SQL Tuesday #39 – PowerShell: How do I use it?

This week’s T-SQL Tuesday is being hosted by Wayne Sheffield ( b | t ) and the topic is using PowerShell for anything.

PowerShell as a solution

Last year I attended a great pre-con by Allen White ( b | t ) for SQL Saturday #126 (Indianapolis, 2012).  Allen White specializes in PowerShell and he started me on my tiny PoSh journey.

PowerShell definitely isn’t the first tool I pick up when I need a solution for SQL Server; In fact, I try to avoid it.  I have been able to use it quite effectively for a few solutions however.  One of the more interesting PoSh scripts I’ve worked up was an attempt to solve the issue with Windows File Caching.  A detailed post can be found here: A potential fix for the file cache issue.  It’s essentially a Windows COM call that is wrapped in C# that is wrapped in PowerShell and it’s purpose is to clear the memory taken up by File Caching in Windows.  If you’re thinking “Why did he do it that way?”, the answer is because I didn’t want to post a compiled .exe and say “just trust me”.

A more recent, and simple, PoSh script I’ve setup is to allow our operations system to automate running a few tasks.  Below is the script that initiates a data load in the system.  It takes the connection information as parameters as well as a region (What set of data to load).  It uses that information to connect and execute a stored procedure.

Wrapping my stored procedure calls in PowerShell allows me to:

  1. Bypass the job software’s built in JDBC connections thus preventing The Shining happening from trying to get it functioning
  2. Parameterize the calls from the software which makes the script maintenance easier and allows testing to happen on different environments
  3. Basic error handling returned to the job software to determine if the call completed or not

The Script

Disable SQL Server Load Settings 
Execute the data load 
#inst = SQL Server instance 
#user = SQL Server username 
#pass = SQL Server password 
#region = What region 

# Get the SQL Server instance name from the command line


$assemblylist =
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
foreach ($asm in $assemblylist)
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)

# Handle any errors that occur
Trap {
# Handle the error
$err = $_.Exception
write-host $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-output $err.Message
#Error, return a 1
Exit 1
# End the script.

#Create a server connection object
$svrConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

#Initiate the object and set the database
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($svrConn)
$db = $srv.Databases["QM_Production_Snapshot"]
#Execute the Stored Procedure
$output = $db.ExecuteNonQuery("EXEC dbo.RefreshTableData '"+$region+"', '%', 1");

#No issues, exit normally
Exit 0

One thought on “T-SQL Tuesday #39 – PowerShell: How do I use it?

  1. Pingback: T-SQL Tuesday #39 – Wrapup | Wayne Sheffield

Leave a Reply