A brief intro of Windows PowerShell
• Windows Powershell is a task automation framework used for its ease of use when compared to building full blown executables.
• With a command line shell that is almost identical to a command window, the tool has an interactive editor that is similar to SQL Server Management Studio’s intellisence for automatic completion of statements.
• The scripting language is based on the .NET Framework and all of its classes, methods, and different data types.
• With a simple UI experience, Powershell is as its name implies: powerful. Users can do .NET programming from a Powershell command without having to build or package anything, giving users access to COM objects and WMI controls.
This also allows for 2 key capabilities:
→ First, it allows for admin task – the core principle of exactly what the tool does.
→ Secondly, it allows users to execute scripts both locally and on remote machines.
Windows PowerShell ISE
I prefer to use the Windows PowerShell ISE when working with PowerShell. It can be searched and opened from the Windows Start menu.
ISE is an acronym for “Integrated Scripting Environment” and it contains the following:
• A command shell
• A command builder that allows users to build commands using a form based tool
• An integrated helper
• A script editor with a debugger
Cmdlets
Cmdlets are at the heart of PowerShell and follow a verb-noun convention, as shown here in these 3 examples.
• Get-ChildItem
• Set-Date
• Format-List
So now that we have background knowledge on the tool, let’s get down to business.
Executing T-SQL queries from the PowerShell ISE
Step 1: Build your connection string to the server and database in which you would like to execute your query.
|
1 2 3 |
$SqlConnection.ConnectionString = "Server=(InsertServerNameHere);Database=(InsertDatabaseNameHere);Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand |
Step 2: Insert your T-SQL query into the $SqlCmd.CommandText space.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$SqlCmd.CommandText = $("INSERT YOUR QUERY HERE") $SqlCmd.Connection = $SqlConnection $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $dataset.Tables[0] |
Step 3: Execute the Powershell script and view them in the Windows Powershell ISE.
Nice work!
In case you were curious like me, a stored procedure with parameters can be executed in a very similar fashion. Let’s continue.
Pulling your T-SQL Query results into a PowerShell array
Building upon the previous 3 steps, users can pump their query results into an array by making a few changes to the script below. At this point we need to set the results to a variable. This is illustrated below by the variable named $Data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=(InsertServerNameHere);Database=(InsertDatabaseNameHere);Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $("INSERT YOUR QUERY HERE") $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $data = $dataset.Tables[0] $Data |
Excellent work today! I will demonstrate how you can begin tapping further into the .NET framework once your data is in an array in part 2 of this blog post.
Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.
