Invoke-Sqlcmd

Runs a script containing statements supported by the SQL Server SQLCMD utility.

Syntax

Invoke-Sqlcmd [[-Query] <String>] [-AbortOnError] [-ConnectionTimeout <Int32>] [-Database <String>]
 [-DedicatedAdministratorConnection] [-DisableCommands] [-DisableVariables] [-EncryptConnection]
 [-ErrorLevel <Int32>] [-HostName <String>] [-IgnoreProviderContext] [-IncludeSqlUserErrors]
 [-InputFile <String>] [-MaxBinaryLength <Int32>] [-MaxCharLength <Int32>] [-NewPassword <String>]
 [-OutputSqlErrors <Boolean>] [-Password <String>] [-QueryTimeout <Int32>] [-ServerInstance <PSObject>]
 [-SeverityLevel <Int32>] [-SuppressProviderContextWarning] [-Username <String>] [-Variable <String[]>]
 [<CommonParameters>]

Description

The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility. The commands supported are Transact-SQL statements and the subset of the XQuery syntax that is supported by the database engine. This cmdlet also accepts many of the commands supported natively by SQLCMD, such as GO and QUIT. This cmdlet also accepts the SQLCMD scripting variables, such as SQLCMDUSER. By default, this cmdlet does not set SQLCMD scripting variables.

This cmdlet does not support the use of commands that are primarily related to interactive script editing. The commands not supported include :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

When this cmdlet is run, the first result set that the script returns is displayed as a formatted table. If subsequent result sets contain different column lists than the first, those result sets are not displayed. If subsequent result sets after the first set have the same column list, their rows are appended to the formatted table that contains the rows that were returned by the first result set.

You can display SQL Server message output, such as those that result from the SQL PRINT statement, by specifying the Verbose parameter.

Examples

Example 1: Connect to a named instance and run a script

PS C:\> Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MainInstance"
 TimeOfQuery
 -----------
 5/13/2010 8:49:43 PM

This command connects to a named instance of the SQL Database Engine on a computer and runs a basic Transact-SQL script.

Example 2: Invoke commands in a script file and save the output in a text file

PS C:\> Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"
Output sent to TestSqlCmd.rpt.

This command reads a file containing Transact-SQL statements and SQLCMD commands, runs the file, and writes the output to another file. The output file may contain proprietary information, so you should secure the output files with the appropriate NTFS permissions.

Example 3: Invoke a script and pass in variable values from a string

PS C:\> $StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
PS C:\> Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2;" -Variable $StringArray
Var1     Var2
----     ----
String1  String2

This command uses an array of character strings as input to the Variable parameter. The array defines multiple SQLCMD variables. The $ signs in the SELECT statement that identify the SQLCMD variables are escaped using the back-tick (`) character.

Example 4: Invoke a script and pass in variables from the SQL database engine

PS C:\>Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
PS C:\>Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('MachineName') AS ComputerName;" -ServerInstance (Get-Item .)
 ComputerName
 ------------
 MyComputer

This command uses Set-Location to navigate to the SQL Server Windows PowerShell provider path for an instance of the SQL Database Engine. Then it calls Get-Item to retrieve a SQL Management Object Server object for use as the ServerInstance parameter of Invoke-Sqlcmd.

Example 5: Run a query and display verbose output

PS C:\> Invoke-Sqlcmd -Query "PRINT N'abc'" -Verbose
VERBOSE: abc

This command uses the Windows PowerShell Verbose parameter to return the message output of the SQL PRINT command.

Example 6: Invoke a command using a positional string as input

PS C:\> Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName;"
 WARNING: Using provider context. Server = MyComputer, Database = AdventureWorks2014. 

 DatabaseName
 ------------
 AdventureWorks2014

This command uses a positional string to supply the input to the Query parameter. It also demonstrates how Invoke-Sqlcmd uses the current path to set the database context to AdventureWorks2014.

Parameters

-AbortOnError
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ConnectionTimeout
Type:Int32
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Database
Type:String
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-DedicatedAdministratorConnection
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-DisableCommands
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-DisableVariables
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-EncryptConnection
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ErrorLevel
Type:Int32
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-HostName
Type:String
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-IgnoreProviderContext
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-IncludeSqlUserErrors
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-InputFile
Type:String
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-MaxBinaryLength
Type:Int32
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-MaxCharLength
Type:Int32
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-NewPassword
Type:String
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-OutputSqlErrors
Type:Boolean
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Password
Type:String
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Query
Type:String
Required:False
Position:1
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-QueryTimeout
Type:Int32
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ServerInstance
Type:PSObject
Required:False
Position:Named
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters:False
-SeverityLevel
Type:Int32
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-SuppressProviderContextWarning
Type:SwitchParameter
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Username
Type:String
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Variable
Type:String[]
Required:False
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

Inputs

PSObject

Outputs

Formatted table