Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Invoke-Sqlcmd cmdlet

Invoke-Sqlcmd cmdlet

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

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"

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.

Path

Database Context

Starts with a drive other than SQLSERVER:

The default database for the login ID in the default instance on the local computer.

SQLSERVER:\SQL

The default database for the login ID in the default instance on the local computer.

SQLSERVER:\SQL\ComputerName

The default database for the login ID in the default instance on the specified computer.

SQLSERVER:\SQL\ComputerName\InstanceName

The default database for the login ID in the specified instance on the specified computer.

SQLSERVER:\SQL\ComputerName\InstanceName\Databases

The default database for the login ID in the specified instance on the specified computer.

SQLSERVER:\SQL\ComputerName\InstanceName\Databases\DatabaseName

The specified database in the specified instance on the specified computer. This also applies to longer paths, such as a path that specifies the Tables and Columns node within a database.

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.

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 follwoing table shows the relationship between the Invoke-Sqlcmd parameters and sqlcmd options:

Description

sqlcmd option

Invoke-Sqlcmd parameter

Server and instance name.

-S

-ServerInstance

The initial database to use.

-d

-Database

Run the specified query and exit.

-Q

-Query

SQL Server Authentication login ID.

-U

-Username

SQL Server Authentication password.

-P

-Password

Variable definition.

-v

-Variable

Query timeout interval.

-t

-QueryTimeout

Stop running on an error

-b

-AbortOnError

Dedicated Administrator Connection.

-A

-DedicatedAdministratorConnection

Disable interactive commands, startup script, and environment variables.

-X

-DisableCommands

Disable variable substitution.

-x

-DisableVariables

Minimum severity level to report.

-V

-SeverityLevel

Minimum error level to report.

-m

-ErrorLevel

Login timeout interval.

-l

-ConnectionTimeout

Hostname.

-H

-HostName

Change password and exit.

-Z

-NewPassword

Input file containing a query

-i

-InputFile

Maximum length of character output.

-w

-MaxCharLength

Maximum length of binary output.

-w

-MaxBinaryLength

Connect using SSL encryption.

No parameter

-EncryptConnection

Display errors

No parameter

-OutputSqlErrors

Output messages to stderr.

-r

No parameter

Use client's regional settings

-R

No parameter

Run the specified query and remain running.

-q

No parameter

Code page to use for output data.

-f

No parameter

Change a password and remain running

-z

No parameter

Packet size

-a

No parameter

Column separator

-s

No parameter

Control output headers

-h

No parameter

Specify control characters

-k

No parameter

Fixed length display width

-Y

No parameter

Variable length display width

-y

No parameter

Echo input

-e

No parameter

Enable quoted identifiers

-I

No parameter

Remove trailing spaces

-W

No parameter

List instances

-L

No parameter

Format output as Unicode

-u

No parameter

Print statistics

-p

No parameter

Command end

-c

No parameter

Connect using Windows Authentication

-E

No parameter

Community Additions

ADD
Show:
© 2015 Microsoft