Using the Invoke-Sqlcmd cmdlet

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"

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"

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 Windows PowerShell back-tick "`" escape character:

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

$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 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 .)

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"

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;"

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

The following commands would return AdventureWorks:

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

Set-Location SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks\Tables\Person.Contact
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

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

Not all of the sqlcmd parameters are needed in a Windows 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

Reference

Concepts