Project Description

Module designed to simplify working with SQL Server from Powershell

Installation

Unzip InvokeSqlQuery.zip into your modules directory: ($Home\Documents\WindowsPowerShell\Modules\InvokeSqlQuery)

Open Powershell and run following command:
Import-Module InvokeSqlQuery

After that, you should be able to run Invoke-SqlQuery cmdlet.

NAME

Invoke-SqlQuery

DESCRIPTION

Executes T-SQL Query on the target server.

This command accepts either query string or query file or both.
You can pass either –Server (with optional –Credential and/or –Database) or –Connection object.

To create connection object, you can use New-SqlConnection command.

Invoke-SqlQuery will execute specified query or queries, using GO as query separator and return strongly types results.
Invokde-SqlQuery supports parameterized queries. In this case you should pass parameters as a dictionary using –Parameters parameter.

SYNTAX

Invoke-SqlQuery [-Query <String>] [-File <FileInfo>] [-Parameters <Hashtable>] [-Server <String>] [-Database <String>] [-Credential <PSCredential>] [-IncludeRecordSetIndex] [-IncludeRecordsCount] [-ConnectionTimeout <Int32>] [-ExecutionTimeout <Int32>]
[-Verbose] [-Debug] [-ErrorAction <ActionPreference>] [-WarningAction <ActionPreference>] [-ErrorVariable <String>] [-WarningVariable <String>] [-OutVariable <String>] [-OutBuffer <Int32>]


Invoke-SqlQuery [-Query <String>] [-File <FileInfo>] [-Parameters <Hashtable>] [-Connection <SqlConnection>] [-IncludeRecordSetIndex] [-IncludeRecordsCount] [-ConnectionTimeout <Int32>] [-ExecutionTimeout <Int32>]
[-Verbose] [-Debug] [-ErrorAction <ActionPreference>] [-WarningAction <ActionPreference>] [-ErrorVariable <String>] [-WarningVariable <String>] [-OutVariable <String>] [-OutBuffer <Int32>]

PARAMETERS

-Query <String>
T-SQL query text. You can pass multiple queries, separated by GO statement, like in sqlcmd and Sql Server Management Studio.
Example:
Invoke-SqlQuery -Query “select 1”

-File <FileInfo>
File (usually .sql) containing one or more T-SQL queries
Example:
$queryFile = Get-Item .\query.sql
Invoke-SqlQuery -File $queryFile

-Parameters <Hashtable>
Parameters for parameterized query
Example:
Invoke-SqlQuery -Query “select * from sys.object where name = @name” –Parameters @{name=’foo’}

-Server <String>
Server name
Example:
Invoke-SqlQuery -Server “mysqlserver”

-Database
<String>
Database name (optional. default value ‘master’)
Example:
Invoke-SqlQuery -Database “foo”

-Credential <PSCredential>
Credentials for Sql Authentication. If not specified, Windows Authentication will be used
Example:
$cred = Get-Credential
Invoke-SqlQuery -Credential $cred

-ConnectionTimeout <int>
Connection timeout

-ExecutionTimeout <int>
Execution timeout

-IncludeRecordSetIndex
Include recordset index for every record. In case of multiple recordsets, that will help to distinguish between them

-IncludeRecordsCount
Returns number of records at the end of recordset

EXAMPLES

==============================================================================
Invoke-SqlQuery -Query "select * from sys.objects where name = 'sysrowsets'" -Server "."
name : sysrowsets
object_id : 5
principal_id :
schema_id : 4
parentobjectid : 0
type : S
typedesc : SYSTEMTABLE
create_date : 7/9/2008 4:19:59 PM
modify_date : 7/9/2008 4:19:59 PM
ismsshipped : True
is_published : False
isschemapublished : False

==============================================================================
Invoke-SqlQuery -IncludeRecordsCount -Query "select * from sys.objects where name = 'sysrowsets'" -Server "."
name : sysrowsets
object_id : 5
principal_id :
schema_id : 4
parentobjectid : 0
type : S
typedesc : SYSTEMTABLE
create_date : 7/9/2008 4:19:59 PM
modify_date : 7/9/2008 4:19:59 PM
ismsshipped : True
is_published : False
isschemapublished : False
(1 row(s) affected)

==============================================================================
Invoke-SqlQuery -Query "select name, type from sys.objects where name = 'sysrowsets'; select 1;" -Server "." -IncludeRecordSetIndex | Format-List
name : sysrowsets
type : S
RecordSetIndex : 0
Column#0 : 1
RecordSetIndex : 1

==============================================================================
Invoke-SqlQuery -Query "select name from sys.objects where name = @Name" -Server "." -Parameter (@{Name='sysrowsets'})
Name

---

sysrowsets