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.
I’m looking for part 2 of this blog. The code is really well laid out and easy to follow. My query will have 2 fields. One is going to be fed into a loop to generate SSRS reports, the 2nd field will be to name the report. I’m just looking for the syntax to take the $Data, which will contain all the IDs and names, from the SQL query, that I need to loop through.
I appreciate your help!
Hi Courtney,
Thank you for your comment and for the nice words! We are working on part 2 of this blog post as we speak. If you’d like, you can subscribe to our blog to receive part 2 the second it comes out.
https://key2consulting.us17.list-manage.com/subscribe?u=ec508b1c07e58967aa37aaece&id=2e2b312f5f
– The Key2 Consulting Team
Would you consider putting together a video that shows both the SQL server side and the Powershell side so we can see how it all ties together?
how to Check whether the DBA have SYSADMIN role onto requested SQL Instance or not?
Script to check sysadmin role..?