powershellscripts.com

Tutorials  PowerShell Cmdlet Help for Invoke-Sqlcmd



NAME
Invoke-Sqlcmd

SYNOPSIS
Runs a script containing statements from the languages (Transact-SQL and XQuery) and commands supported by the SQL
Server sqlcmd utility.

SYNTAX
Invoke-Sqlcmd [-ServerInstance ] [-Database ] [-EncryptConnection] [-Username ] [-Passwor
d ] [[-Query] ] [-QueryTimeout ] [-ConnectionTimeout ] [-ErrorLevel ] [-Severi
tyLevel ] [-MaxCharLength ] [-MaxBinaryLength ] [-AbortOnError] [-DedicatedAdministratorConnec
tion] [-DisableVariables] [-DisableCommands] [-HostName ] [-NewPassword ] [-Variable ] [-
InputFile ] [-OutputSqlErrors] [-SuppressProviderContextWarning] [-IgnoreProviderContext] [ s>]


DESCRIPTION
Runs a script containing the languages and commands supported by the SQL Server sqlcmd utility. The languages suppo
rted are Transact-SQL and the XQuery syntax supported by the Database Engine. Invoke-Sqlcmd also accepts many of th
e commands supported by sqlcmd, such as GO and QUIT. Invoke-Sqlcmd accepts the sqlcmd scripting variables, such as
SQLCMDUSER. Invoke-Sqlcmd does not set sqlcmd scripting variables by default.
Invoke-Sqlcmd does not support the sqlcmd commands primarily related to interactive script editing. The commands no
t supported include :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.
The first result set the script returns is displayed as a formatted table. Result sets after the first are not disp
layed if their column list is different from the column list of the first result set. If result sets after the firs
t set have the same column list, their rows are appended to the formatted table that contains the rows that were re
turned by the first result set.
Invoke-Sqlcmd does not return message output, such as the output of PRINT statements, unless you use the PowerShell
-Verbose parameter.


PARAMETERS
-ServerInstance
Character string or SMO server object specifying the name of an instance of the Database Engine. For default in
stances, only specify the computer name: "MyComputer". For named instances, use the format "ComputerName\Instan
ceName".

Required? false
Position? named
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false

-Database
A character string specifying the name of a database. Invoke-Sqlcmd connects to this database in the instance t
hat is specified in -ServerInstance.
If -Database is not specified, the database that is used depends on whether the current path specifies both the
SQLSERVER:\SQL folder and a database name. If the path specifies both the SQL folder and a database name, Invo
ke-Sqlcmd connects to the database that is specified in the path. If the path is not based on the SQL folder, o
r the path does not contain a database name, Invoke-Sqlcmd connects to the default database for the current log
in ID. If you specify the -IgnoreProviderContext switch, Invoke-Sqlcmd does not consider any database specified
in the current path, and connects to the database defined as the default for the current login ID.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-EncryptConnection
Specifies whether Invoke-Sqlcmd uses Secure Sockets Layer (SSL) encryption for the connection to the instance o
f the Database Engine specified in -ServerInstance. If TRUE is specified SSL encryption is used. If FALSE is sp
ecified encryption is not used.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-Username
Specifies the login ID for making a SQL Server Authentication connection to an instance of the Database Engine.
The password must be specified using -Password. If -Username and -Password are not specified, Invoke-Sqlcmd at
tempts a Windows Authentication connection using the Windows account running the PowerShell session.
When possible, use Windows Authentication.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-Password
Specifies the password for the SQL Server Authentication login ID that was specified in -Username. Passwords ar
e case-sensitive.
When possible, use Windows Authentication.
Do not use a blank password, when possible use a strong password. For more information, see "Strong Password" i
n SQL Server Books Online.

SECURITY NOTE: If you type -Password followed by your password, the password is visible to anyone who can see y
our monitor. If you code -Password followed by your password in a .ps1 script, anyone reading the script file w
ill see your password. Assign the appropriate NTFS permissions to the file to prevent other users from being ab
le to read the file.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-Query
Specifies one or more queries to be run. The queries can be Transact-SQL or XQuery statements, or sqlcmd comman
ds. 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] inste
ad of quoted identifiers such as "MyTable".

Required? false
Position? 1
Default value
Accept pipeline input? false
Accept wildcard characters? false

-QueryTimeout
Specifies the number of seconds before the queries time out. If a timeout value is not specified, the queries d
o not time out. The timeout must be an integer between 1 and 65535.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-ConnectionTimeout
Specifies the number of seconds when Invoke-Sqlcmd times out if it cannot successfully connect to an instance o
f 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? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-ErrorLevel
Specifies that Invoke-Sqlcmd display only error messages whose severity level is equal to or higher than the va
lue specified. All error messages are displayed if -ErrorLevel is not specified or set to 0. Database Engine er
ror severities range from 1 to 24.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-SeverityLevel
Specifies the lower limit for the error message severity level Invoke-Sqlcmd returns to the ERRORLEVEL PowerShe
ll variable. Invoke-Sqlcmd returns the highest severity level from the error messages generated by the queries
it runs, provided that severity is equal to or higher than -SeverityLevel. If -SeverityLevel is not specified o
r set to 0, Invoke-Sqlcmd returns 0 to ERRORLEVEL. The severity levels of Database Engine error messages range
from 1 to 24. Invoke-Sqlcmd does not report severities for informational messages that have a severity of 10.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-MaxCharLength
Specifies the maximum number of characters returned for columns with character or Unicode data types, such as c
har, nchar, varchar, and nvarchar. The default is 4,000 characters.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-MaxBinaryLength
Specifies the maximum number of bytes returned for columns with binary string data types, such as binary and va
rbinary. The default is 1,024 bytes.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-AbortOnError
Specifies that Invoke-Sqlcmd stop running and return an error level to the PowerShell ERRORLEVEL variable if In
voke-Sqlcmd encounters an error. The error level returned is 1 if the error has a severity higher than 10, and
the error level is 0 if the error has a severity of 10 or less. If -ErrorLevel is also specified, Invoke-Sqlcmd
returns 1 only if the error message severity is also equal to or higher than the value specified for -ErrorLev
el.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-DedicatedAdministratorConnection
Uses a Dedicated Administrator Connection (DAC) to connect to an instance of the Database Engine. DAC is used b
y system administrators for actions such as troubleshooting instances that will not accept new standard connect
ions. The instance must be configured to support DAC. If DAC is not enabled, Invoke-Sqlcmd reports an error and
stops running.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-DisableVariables
Specifies that Invoke-Sqlcmd ignore sqlcmd scripting variables. This is useful when a script contains many INSE
RT statements that may contain strings that have the same format as variables, such as $(variable_name).

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-DisableCommands
Turns off some sqlcmd features that might compromise security when run in batch files. It prevents PowerShell v
ariables from being passed in to the Invoke-Sqlcmd script. The startup script specified in the SQLCMDINI script
ing variable is not run.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-HostName
Specifies a workstation name. The workstation name is reported by the sp_who system stored procedure and in the
hostname column of the sys.processes catalog view. If HostName is not specified, the default is the name of th
e computer on which Invoke-Sqlcmd is running. HostName can be used to identify different Invoke-Sqlcmd sessions
.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-NewPassword
Specifies a new password for a SQL Server Authentication login ID. Invoke-Sqlcmd changes the password and then
exits. -Username and -Password must also be specified, with -Password specifying the current password for the l
ogin.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-Variable
Creates a sqlcmd scripting variable for use in the sqlcmd script, and sets a value for the variable. Use a Powe
rShell array to specify multiple variables and their values.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-InputFile
Specifies a file to be used as the query input to Invoke-Sqlcmd. The file can contain Transact-SQL statements,
XQuery statements, and sqlcmd commands and scripting variables. Specify the full path to the file. Spaces are n
ot allowed in the file path or file name.
Only run scripts from trusted sources. Ensure all input scripts are secured with the appropriate NTFS permissio
ns.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-OutputSqlErrors
Specifies whether error messages are displayed in the Invoke-Sqlcmd output.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-SuppressProviderContextWarning
Suppresses the warning that Invoke-Sqlcmd has used the database context from the current SQLSERVER:\SQL path se
tting to establish the database context for the cmdlet.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-IgnoreProviderContext
Invoke-Sqlcmd ignores the database context that was established by the current SQLSERVER:\SQL path. If the -Dat
abase parameter is not specified, Invoke-Sqlcmd uses the default database for the current login ID or Windows a
ccount.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false


This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer and OutVariable. For more information, type,
"get-help about_commonparameters".

INPUTS
PSObject


OUTPUTS
Formatted table


NOTES




-------------- Example 1 --------------

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


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


TimeOfQuery
-----------
10/7/2007 1:04:20 PM


-------------- Example 2 --------------

C:\PS>Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSqlCmd.sql" | Out-File -filePath "C:\MyFolder\TestSqlCmd.rpt"


This example reads a file containing Transact-SQL statements and sqlcmd commands, runs the file, and writes the out
put to another file. Ensure all output files are secured with the appropriate NTFS permissions.


Output sent to TestSqlCmd.rpt.


-------------- Example 3 --------------

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


This example uses an array of character strings as input to the -Variable parameter. The array defines multiple sql
cmd variables. The $ signs in the SELECT statement that identify the sqlcmd variables are escaped using the back-ti
ck (`) character.


Var1 Var2
---- ----
String1 String2


-------------- Example 4 --------------

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


This example uses Set-Location to navigate to the SQL Server PowerShell provider path for an instance of the Databa
se Engine. Then the example uses Get-Item to retrieve an SMO Server object for use as the -ServerInstance parameter
of Invoke-Sqlcmd.


TimeOfQuery
-----------
10/18/2007 8:49:43 PM


-------------- Example 5 --------------

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


This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.


VERBOSE: abc


-------------- Example 6 --------------

C:\PS>Set-Location SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks
Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName;"


This examples uses a positional string to supply the input to the -Query parameter. It also shows how Invoke-Sqlcmd
uses the current path to set the database context to AdventureWorks.


WARNING: Using provider context. Server = MyComputer, Database = AdventureWorks.

DatabaseName
------------
AdventureWorks



RELATED LINKS
SQL Server Books Online: Transact-SQL Reference
SQL Server Books Online: sqlcmd Utility
SQL Server Books Online: XQuery Reference