Give me some DATA! (with Powershell)
Ah…wow.. has it really been over a YEAR since I last post? Sorry about that, but it has been an absolutely nutty year.
Anyway..
In my new job and new role, I get to have fun showing people the power of automation with Powershell.
One of the recent tasks I was helping with is to take a list of users (from AD) and query a database to get some info about these users.
While I had done some work with SQL queries with Powershell, I hadn’t ever “functionalized” it, so I decided to take this opportunity to do so.
While I am sure there are a bazillion examples of this out there, this is what I came up with and here is what it looks like.
Sorry there aren’t many (any) in-line comments like I normally do, but if you have questions, please let me know!
Thanks and hope you find this helpful.
As always… Happy Scripting!
– Mark
##------------------------------------------------------------------- ## Script: Get-SQLData.ps1 ## Written by: Mark A. Weaver ## Website: vmweaver.com ## ## Version: 1.0 ## Creation Date: 3/16/2011 ## Purpose: This is really a function that you pass 3 parameters to. It will connect to the ## SQL Server you specify, to the Database you specify, and execute the ## query you specify. ## This connects with you existing credentials (integrated). ## ## ## Input: ## -SQLServer "Name of your sql server" ## -Database "Name of your database" ## -Query "your query" ## *Note if you want to have a query that spans multiple lines, etc, you can pass in a ## 'here-string' or construct another multi-line (not array) string. ## ## ## Output: ## An array is passed back containing the data from your query. ## If no data is returned, the array will be $null ## ## ## Assumptions: ## ## ############################# ## Updates: ## ## ##---------------------------------------------------------------------- Function Get-SQLData { param ([string]$SQLServer, [string]$Database, [string]$Query) $SQLData=@() $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $Database; Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null $SqlConnection.Close() $RecordCount = ($DataSet.tables[0] | Measure-Object).count if ($RecordCount -eq 0) { $SQLData = $Null } else { $SQLData = $DataSet.tables[0] } return $SQLData }