# encoding: ascii
# api: powershell
# title: Invoke-SqlCommand.ps1
# description: From Windows PowerShell Cookbook (O’Reilly) by Lee Holmes
# version: 8.0
# author: Lee Holmes
# license: CC0
# x-poshcode-id: 2188
# x-archived: 2016-03-19T02:53:16
# x-published: 2011-09-09T21:41:00
#
#
##############################################################################
##
## Invoke-SqlCommand
##
## From Windows PowerShell Cookbook (O'Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
##
##############################################################################
<#
.SYNOPSIS
Return the results of a SQL query or operation
.EXAMPLE
Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
Invokes a command using Windows authentication
.EXAMPLE
PS >$cred = Get-Credential
PS >Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
Invokes a command using SQL Authentication
.EXAMPLE
PS >$server = "MYSERVER"
PS >$database = "Master"
PS >$sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
PS >Invoke-SqlCommand $server $database $sql
Invokes a command that performs an update
.EXAMPLE
PS >$sql = "EXEC SalesByCategory 'Beverages'"
PS >Invoke-SqlCommand -Sql $sql
Invokes a stored procedure
.EXAMPLE
Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
Access an Access database
.EXAMPLE
Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]'
Access an Excel file
#>
param(
## The data source to use in the connection
[string] $DataSource = ".\SQLEXPRESS",
## The database within the data source
[string] $Database = "Northwind",
## The SQL statement(s) to invoke against the database
[Parameter(Mandatory = $true)]
[string[]] $SqlCommand,
## The timeout, in seconds, to wait for the query to complete
[int] $Timeout = 60,
## The credential to use in the connection, if any.
$Credential
)
Set-StrictMode -Version Latest
## Prepare the authentication information. By default, we pick
## Windows authentication
$authentication = "Integrated Security=SSPI;"
## If the user supplies a credential, then they want SQL
## authentication
if($credential)
{
$credential = Get-Credential $credential
$plainCred = $credential.GetNetworkCredential()
$authentication =
("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
}
## Prepare the connection string out of the information they
## provide
$connectionString = "Provider=sqloledb; " +
"Data Source=$dataSource; " +
"Initial Catalog=$database; " +
"$authentication; "
## If they specify an Access database or Excel file as the connection
## source, modify the connection string to connect to that data source
if($dataSource -match '\.xls$|\.mdb$')
{
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=$dataSource; "
if($dataSource -match '\.xls$')
{
$connectionString += 'Extended Properties="Excel 8.0;"; '
## Generate an error if they didn't specify the sheet name properly
if($sqlCommand -notmatch '\[.+\$\]')
{
$error = 'Sheet names should be surrounded by square brackets, ' +
'and have a dollar sign at the end: [Sheet1$]'
Write-Error $error
return
}
}
}
## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$connection.Open()
foreach($commandString in $sqlCommand)
{
$command = New-Object Data.OleDb.OleDbCommand $commandString,$connection
$command.CommandTimeout = $timeout
## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
## Return all of the rows from their query
$dataSet.Tables | Select-Object -Expand Rows
}
$connection.Close()