Interacting with a SQL database from PowerShell is pretty simple.  In the example I give below, it is assumed that 1) the appropriate ODBC data source has been configured on the machine from which the commands are being executed, and 2) the account under which the script commands are being run has the appropriate privileges on the SQL database.

First of all, let’s define a few variables:

$dataSource = “sqlserver.domain.com”
$database = “MyDB”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “

Now, here’s the really critical variable: the SQL query itself.

$sql = “SELECT * FROM tbl_MyTable”

Now for the heavy lifting, where we instantiate an OleDb database connection object, populate it with the appropriate parameters, and tell it to do its thing:

$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)

That really is all that there is to it. The returned rows are all in the $rows variable, ready for whatever munging you need to do.

Happy PowerShelling….