PowerShell – ODBC SQL Query

Hre is an Example SQL Query Using ODBC and Powershell

The key bits there is to ensure the SQL Query  (in my example SELECT PrinterId,DeviceName,Description,ServerDefault  FROM $SQLDBName.dbo.Printer where Available = 1 ) actually works correctly in SQL Server Management Studio, with the credentials you are going to use with the ODBC system DSN.

In the example below, I am wanting a listed of printers from the table $SQLDBName.dbo.Printer where the field  “Available” equals 1 and returning to the $Result object the fields  PrinterId,DeviceName,Description,ServerDefault, as well as a bunch of other info automatically injected into the object (do a $Result |get-member to poke around)

I am then taking just the object $Result.rows, which funnily enough contains the rows from the SQL select statement

 

$SqlServer = "Server1"
$SQLDBName = "SMSDB"
$SQLUserName = "domain\username"
$SQLPassword = "MyPassword"

function Get-SQLResult ()
{

$SqlQuery = "SELECT PrinterId,DeviceName,Description,ServerDefault  FROM $SQLDBName.dbo.Printer where Available = 1"


$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SQLDBName; Integrated Security = False ;UID = $SQLUserName;Password = $SQLPassword"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()
$DataSet.Tables

}
$Result = Get-Get-SQLResult
$c = $Result.rows



$PrintQueuesToInstall = $c.DeviceName

 

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.