< Back
Invoke-Sqlcmd2
Post
NAME Invoke-Sqlcmd2
SYNOPSIS
Runs a T-SQL script.
SYNTAX
Invoke-Sqlcmd2 [-ServerInstance] <String[]> [[-Database] <String>] [-Query] <String> [[-Credential]
<PSCredential>] [[-Encrypt]] [[-QueryTimeout] <Int32>] [[-ConnectionTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [<CommonParameters>]
Invoke-Sqlcmd2 [-ServerInstance] <String[]> [[-Database] <String>] [-InputFile] <String> [[-Credential]
<PSCredential>] [[-Encrypt]] [[-QueryTimeout] <Int32>] [[-ConnectionTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [<CommonParameters>]
Invoke-Sqlcmd2 [[-Database] <String>] [-Query] <String> [[-QueryTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [[-SQLConnection] <SqlConnection>] [<CommonParameters>]
Invoke-Sqlcmd2 [[-Database] <String>] [-InputFile] <String> [[-QueryTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [[-SQLConnection] <SqlConnection>] [<CommonParameters>]
DESCRIPTION
Runs a T-SQL script. Invoke-Sqlcmd2 runs the whole scipt and only captures the first selected result set, such as
the output of PRINT statements when -verbose parameter is specified.
Paramaterized queries are supported.
Help details below borrowed from Invoke-Sqlcmd
PARAMETERS
-ServerInstance <String[]>
One or more ServerInstances to query. For default instances, only specify the computer name: "MyComputer". For
named instances, use the format "ComputerName\\InstanceName".
Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue, ByPropertyName)
Accept wildcard characters? false
-Database <String>
A character string specifying the name of a database. Invoke-Sqlcmd2 connects to this database in the instance
that is specified in -ServerInstance.
If a SQLConnection is provided, we explicitly switch to this database
Required? false
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Query <String>
Specifies one or more queries to be run. The queries can be Transact-SQL (? or XQuery statements, or sqlcmd
commands. Multiple queries separated by a semicolon can be specified. Do not specify the sqlcmd GO separator.
Escape any double quotation marks included in the string ?). Consider using bracketed identifiers such as
[MyTable] instead of quoted identifiers such as "MyTable".
Required? true
Position? 3
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-InputFile <String>
Specifies a file to be used as the query input to Invoke-Sqlcmd2. The file can contain Transact-SQL
statements, (? XQuery statements, and sqlcmd commands and scripting variables ?). Specify the full path to the
file.
Required? true
Position? 3
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Credential <PSCredential>
Specifies A PSCredential for SQL Server Authentication connection to an instance of the Database Engine.
If -Credential is not specified, Invoke-Sqlcmd attempts a Windows Authentication connection using the Windows
account running the PowerShell session.
SECURITY NOTE: If you use the -Debug switch, the connectionstring including plain text password will be sent
to the debug stream.
Required? false
Position? 4
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Encrypt [<SwitchParameter>]
If specified, will request that the connection to the SQL is done over SSL. This requires that the SQL Server
has been set up to accept SSL requests. For information regarding setting up SSL on SQL Server, visit this
link: https://technet.microsoft.com/en-us/lib ... .105).aspx
Required? false
Position? 5
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-QueryTimeout <Int32>
Specifies the number of seconds before the queries time out.
Required? false
Position? 6
Default value 600
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-ConnectionTimeout <Int32>
Specifies the number of seconds when Invoke-Sqlcmd2 times out if it cannot successfully connect to an instance
of the Database Engine. The timeout value must be an integer between 0 and 65534. If 0 is specified,
connection attempts do not time out.
Required? false
Position? 7
Default value 15
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-As <String>
Specifies output type - DataSet, DataTable, array of DataRow, PSObject or Single Value
PSObject output introduces overhead but adds flexibility for working with results:
http://powershell.org/wp/forums/topic/d ... th-dbnull/
Required? false
Position? 8
Default value DataRow
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-SqlParameters <IDictionary>
Hashtable of parameters for parameterized SQL queries.
http://blog.codinghorror.com/give-me-pa ... -me-death/
Example:
-Query "SELECT ServerName FROM tblServerInfo WHERE ServerName LIKE @ServerName"
-SqlParameters @{"ServerName = "c-is-hyperv-1"}
Required? false
Position? 9
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-AppendServerInstance [<SwitchParameter>]
If specified, append the server instance to PSObject and DataRow output
Required? false
Position? 10
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-SQLConnection <SqlConnection>
If specified, use an existing SQLConnection.
We attempt to open this connection if it is closed
Required? false
Position? 11
Default value
Accept pipeline input? false
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
None
You cannot pipe objects to Invoke-Sqlcmd2
OUTPUTS
As PSObject: System.Management.Automation.PSCustomObject
As DataRow: System.Data.DataRow
As DataTable: System.Data.DataTable
As DataSet: System.Data.DataTableCollectionSystem.Data.DataSet
As SingleValue: Dependent on data type in first column.
NOTES
Changelog moved to CHANGELOG.md:
https://github.com/sqlcollaborative/Inv ... ANGELOG.md
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance "MyComputer\\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM
sysprocesses WHERE spid = 1"
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
StartTime
-----------
2010-08-12 21:21:03.593
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance "MyComputer\\MyInstance" -InputFile "C:\\MyFolder\\tsqlscript.sql" | Out-File
-filePath "C:\\MyFolder\\tsqlscript.rpt"
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance "MyComputer\\MyInstance" -Query "PRINT 'hello world'" -Verbose
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance MyServer\\MyInstance -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo"
-as PSObject | ?{$_.VCNumCPU -gt 8}
Invoke-Sqlcmd2 -ServerInstance MyServer\\MyInstance -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo" -as
PSObject | ?{$_.VCNumCPU}
This example uses the PSObject output type to allow more flexibility when working with results.
If we used DataRow rather than PSObject, we would see the following behavior:
Each row where VCNumCPU does not exist would produce an error in the first example
Results would include rows where VCNumCPU has DBNull value in the second example
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>'Instance1', 'Server1/Instance1', 'Server2' | Invoke-Sqlcmd2 -query "Sp_databases" -as psobject
-AppendServerInstance
This example lists databases for each instance. It includes a column for the ServerInstance in question.
DATABASE_NAME DATABASE_SIZE REMARKS ServerInstance
------------- ------------- ------- --------------
REDACTED 88320 Instance1
master 17920 Instance1
...
msdb 618112 Server1/Instance1
tempdb 563200 Server1/Instance1
...
OperationsManager 20480000 Server2
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>#Construct a query using SQL parameters
$Query = "SELECT ServerName, VCServerClass, VCServerContact FROM tblServerInfo WHERE VCServerContact LIKE
@VCServerContact AND VCServerClass LIKE @VCServerClass"
#Run the query, specifying values for SQL parameters
Invoke-Sqlcmd2 -ServerInstance SomeServer\\NamedInstance -Database ServerDB -query $query -SqlParameters @{
VCServerContact="%cookiemonster%"; VCServerClass="Prod" }
ServerName VCServerClass VCServerContact
---------- ------------- ---------------
SomeServer1 Prod cookiemonster, blah
SomeServer2 Prod cookiemonster
SomeServer3 Prod blah, cookiemonster
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Invoke-Sqlcmd2 -SQLConnection $Conn -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid =
1"
This example uses an existing SQLConnection and runs a basic T-SQL query against it
StartTime
-----------
2010-08-12 21:21:03.593
RELATED LINKS
https://github.com/sqlcollaborative/Invoke-SqlCmd2
https://github.com/RamblingCookieMonster/PowerShell
New-SQLConnection
SYNOPSIS
Runs a T-SQL script.
SYNTAX
Invoke-Sqlcmd2 [-ServerInstance] <String[]> [[-Database] <String>] [-Query] <String> [[-Credential]
<PSCredential>] [[-Encrypt]] [[-QueryTimeout] <Int32>] [[-ConnectionTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [<CommonParameters>]
Invoke-Sqlcmd2 [-ServerInstance] <String[]> [[-Database] <String>] [-InputFile] <String> [[-Credential]
<PSCredential>] [[-Encrypt]] [[-QueryTimeout] <Int32>] [[-ConnectionTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [<CommonParameters>]
Invoke-Sqlcmd2 [[-Database] <String>] [-Query] <String> [[-QueryTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [[-SQLConnection] <SqlConnection>] [<CommonParameters>]
Invoke-Sqlcmd2 [[-Database] <String>] [-InputFile] <String> [[-QueryTimeout] <Int32>] [[-As] <String>]
[[-SqlParameters] <IDictionary>] [[-AppendServerInstance]] [[-SQLConnection] <SqlConnection>] [<CommonParameters>]
DESCRIPTION
Runs a T-SQL script. Invoke-Sqlcmd2 runs the whole scipt and only captures the first selected result set, such as
the output of PRINT statements when -verbose parameter is specified.
Paramaterized queries are supported.
Help details below borrowed from Invoke-Sqlcmd
PARAMETERS
-ServerInstance <String[]>
One or more ServerInstances to query. For default instances, only specify the computer name: "MyComputer". For
named instances, use the format "ComputerName\\InstanceName".
Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue, ByPropertyName)
Accept wildcard characters? false
-Database <String>
A character string specifying the name of a database. Invoke-Sqlcmd2 connects to this database in the instance
that is specified in -ServerInstance.
If a SQLConnection is provided, we explicitly switch to this database
Required? false
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Query <String>
Specifies one or more queries to be run. The queries can be Transact-SQL (? or XQuery statements, or sqlcmd
commands. Multiple queries separated by a semicolon can be specified. Do not specify the sqlcmd GO separator.
Escape any double quotation marks included in the string ?). Consider using bracketed identifiers such as
[MyTable] instead of quoted identifiers such as "MyTable".
Required? true
Position? 3
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-InputFile <String>
Specifies a file to be used as the query input to Invoke-Sqlcmd2. The file can contain Transact-SQL
statements, (? XQuery statements, and sqlcmd commands and scripting variables ?). Specify the full path to the
file.
Required? true
Position? 3
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Credential <PSCredential>
Specifies A PSCredential for SQL Server Authentication connection to an instance of the Database Engine.
If -Credential is not specified, Invoke-Sqlcmd attempts a Windows Authentication connection using the Windows
account running the PowerShell session.
SECURITY NOTE: If you use the -Debug switch, the connectionstring including plain text password will be sent
to the debug stream.
Required? false
Position? 4
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Encrypt [<SwitchParameter>]
If specified, will request that the connection to the SQL is done over SSL. This requires that the SQL Server
has been set up to accept SSL requests. For information regarding setting up SSL on SQL Server, visit this
link: https://technet.microsoft.com/en-us/lib ... .105).aspx
Required? false
Position? 5
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-QueryTimeout <Int32>
Specifies the number of seconds before the queries time out.
Required? false
Position? 6
Default value 600
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-ConnectionTimeout <Int32>
Specifies the number of seconds when Invoke-Sqlcmd2 times out if it cannot successfully connect to an instance
of the Database Engine. The timeout value must be an integer between 0 and 65534. If 0 is specified,
connection attempts do not time out.
Required? false
Position? 7
Default value 15
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-As <String>
Specifies output type - DataSet, DataTable, array of DataRow, PSObject or Single Value
PSObject output introduces overhead but adds flexibility for working with results:
http://powershell.org/wp/forums/topic/d ... th-dbnull/
Required? false
Position? 8
Default value DataRow
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-SqlParameters <IDictionary>
Hashtable of parameters for parameterized SQL queries.
http://blog.codinghorror.com/give-me-pa ... -me-death/
Example:
-Query "SELECT ServerName FROM tblServerInfo WHERE ServerName LIKE @ServerName"
-SqlParameters @{"ServerName = "c-is-hyperv-1"}
Required? false
Position? 9
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-AppendServerInstance [<SwitchParameter>]
If specified, append the server instance to PSObject and DataRow output
Required? false
Position? 10
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-SQLConnection <SqlConnection>
If specified, use an existing SQLConnection.
We attempt to open this connection if it is closed
Required? false
Position? 11
Default value
Accept pipeline input? false
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
None
You cannot pipe objects to Invoke-Sqlcmd2
OUTPUTS
As PSObject: System.Management.Automation.PSCustomObject
As DataRow: System.Data.DataRow
As DataTable: System.Data.DataTable
As DataSet: System.Data.DataTableCollectionSystem.Data.DataSet
As SingleValue: Dependent on data type in first column.
NOTES
Changelog moved to CHANGELOG.md:
https://github.com/sqlcollaborative/Inv ... ANGELOG.md
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance "MyComputer\\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM
sysprocesses WHERE spid = 1"
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
StartTime
-----------
2010-08-12 21:21:03.593
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance "MyComputer\\MyInstance" -InputFile "C:\\MyFolder\\tsqlscript.sql" | Out-File
-filePath "C:\\MyFolder\\tsqlscript.rpt"
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance "MyComputer\\MyInstance" -Query "PRINT 'hello world'" -Verbose
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Invoke-Sqlcmd2 -ServerInstance MyServer\\MyInstance -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo"
-as PSObject | ?{$_.VCNumCPU -gt 8}
Invoke-Sqlcmd2 -ServerInstance MyServer\\MyInstance -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo" -as
PSObject | ?{$_.VCNumCPU}
This example uses the PSObject output type to allow more flexibility when working with results.
If we used DataRow rather than PSObject, we would see the following behavior:
Each row where VCNumCPU does not exist would produce an error in the first example
Results would include rows where VCNumCPU has DBNull value in the second example
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>'Instance1', 'Server1/Instance1', 'Server2' | Invoke-Sqlcmd2 -query "Sp_databases" -as psobject
-AppendServerInstance
This example lists databases for each instance. It includes a column for the ServerInstance in question.
DATABASE_NAME DATABASE_SIZE REMARKS ServerInstance
------------- ------------- ------- --------------
REDACTED 88320 Instance1
master 17920 Instance1
...
msdb 618112 Server1/Instance1
tempdb 563200 Server1/Instance1
...
OperationsManager 20480000 Server2
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>#Construct a query using SQL parameters
$Query = "SELECT ServerName, VCServerClass, VCServerContact FROM tblServerInfo WHERE VCServerContact LIKE
@VCServerContact AND VCServerClass LIKE @VCServerClass"
#Run the query, specifying values for SQL parameters
Invoke-Sqlcmd2 -ServerInstance SomeServer\\NamedInstance -Database ServerDB -query $query -SqlParameters @{
VCServerContact="%cookiemonster%"; VCServerClass="Prod" }
ServerName VCServerClass VCServerContact
---------- ------------- ---------------
SomeServer1 Prod cookiemonster, blah
SomeServer2 Prod cookiemonster
SomeServer3 Prod blah, cookiemonster
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Invoke-Sqlcmd2 -SQLConnection $Conn -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid =
1"
This example uses an existing SQLConnection and runs a basic T-SQL query against it
StartTime
-----------
2010-08-12 21:21:03.593
RELATED LINKS
https://github.com/sqlcollaborative/Invoke-SqlCmd2
https://github.com/RamblingCookieMonster/PowerShell
New-SQLConnection