Invoke-Sqlcmd cmdlet

Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility.

Using Invoke-Sqlcmd

The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a Windows PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.

This is an example of calling Invoke-Sqlcmd to execute a simple query, similar to specifying sqlcmd with the -Q and -S options:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"  

This is an example of calling Invoke-Sqlcmd, specifying an input file and piping the output to a file This is similar to specifying sqlcmd with the -i and -o options:

Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -FilePath "C:\MyFolder\TestSQLCmd.rpt"  

This is an example of using a Windows PowerShell array to pass multiple sqlcmd scripting variables to Invoke-Sqlcmd. The "$" characters identifying the sqlcmd scripting variables in the SELECT statement have been escaped by using the PowerShell back-tick "`" escape character:

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"  
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray  

This is an example of using the SQL Server provider for Windows PowerShell to navigate to an instance of the Database Engine, and then using the Windows PowerShell Get-Item cmdlet to retrieve the SMO Server object for the instance and passing it to Invoke-Sqlcmd:

Set-Location SQLSERVER:\SQL\MyComputer\MyInstance  
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance (Get-Item .)  

The -Query parameter is positional and does not have to be named. If the first string that is passed to Invoke-Sqlcmd: is unnamed, it is treated as the -Query parameter.

Invoke-Sqlcmd "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"  

Path Context in Invoke-Sqlcmd

If you do not use the -Database parameter, the database context for Invoke-Sqlcmd is set by the path that is active when the cmdlet is called.

For example, assume that the default database for your Windows account in the default instance of the local computer is master. Then, the following commands would return master:

Set-Location SQLSERVER:\SQL  
Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName;"  

The following commands would return AdventureWorks2012:

Set-Location SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2012\Tables\Person.Person  
Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName;"  

Invoke-Sqlcmd provides a warning when it uses the path database context. You can use the -SuppressProviderContextWarning parameter to turn off the warning message. You can use the -IgnoreProviderContext parameter to tell Invoke-Sqlcmd to always use the default database for the login.

Comparing Invoke-Sqlcmd and the sqlcmd Utility

Invoke-Sqlcmd can be used to run many of the scripts that can be run using the sqlcmd utility. However, Invoke-Sqlcmd runs in a Windows PowerShell environment which is different than the command prompt environment that sqlcmd is run in. The behavior of Invoke-Sqlcmd has been modified to work in a Windows PowerShell environment.

Not all of the sqlcmd commands are implemented in Invoke-Sqlcmd. Commands that are not implemented include the following: :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

Invoke-Sqlcmd does not initialize the sqlcmd environment or scripting variables such as SQLCMDDBNAME or SQLCMDWORKSTATION.

Invoke-Sqlcmd does not display messages, such as the output of PRINT statements, unless you specify the Windows PowerShell -Verbose common parameter. For example:

Invoke-Sqlcmd -Query "PRINT N'abc';" -Verbose  

Not all of the sqlcmd parameters are needed in a PowerShell environment. For example, Windows PowerShell formats all output from cmdlets, so the sqlcmd parameters specifying formatting options are not implemented in Invoke-Sqlcmd. The following table shows the relationship between the Invoke-Sqlcmd parameters and sqlcmd options:

See Also

Use the Database Engine cmdlets
sqlcmd Utility
Use the sqlcmd Utility