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