SqlCmd Properties

	Name	Description
	CommandLineQuery	Gets or sets one or more command line queries to execute when SqlCmd starts, but does not exit sqlcmd when the query has finished running.
	Database	Gets or sets the name of the database to connect to. Issues a Copy USE db_name statement when you start SqlCmd. This option sets the SqlCmd scripting variable SQLCMDDBNAME. This specifies the initial database. The default is your login's default-database property. If the database does not exist, an error message is generated and SqlCmd exits.
	DedicatedAdminConnection	Gets or sets a flag that indicates if the connection to SQL Server should use a Dedicated Administrator Connection (DAC). This kind of connection is used to troubleshoot a server. This will only work with server computers that support DAC. If DAC is not available, SqlCmd generates an error message and then exits. For more information about DAC, see Using a Dedicated Administrator Connection.
	DisableVariableSubstitution	Causes SqlCmd to ignore scripting variables. This is useful when a script contains many INSERT statements that may contain strings that have the same format as regular variables, such as $(variable_name).
	EchoInput	Gets or sets a flag that indicates if the input scripts are written to the standard output device (stdout).
	EnableQuotedIdentifiers	Gets or sets a flag that sets the Copy SET QUOTED_IDENTIFIER connection option to Copy ON . By default, it is set to Copy OFF . For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
	Headers	Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. This option sets the sqlcmd scripting variable SQLCMDHEADERS. Use -1 to specify that headers must not be printed. Any value that is not valid causes SqlCmd to generate an error message and then exit.
	InputFiles	Gets or sets the path to a file that contains a batch of SQL statements. Multiple files may be specified that will be read and processed in order. Do not use any spaces between the file names. SqlCmd will first check to see whether all files exist. If one or more files do not exist, SqlCmd will exit. The InputFiles and CommandLineQuery options are mutually exclusive. Please note that if you provide a large number of files, you may exceed the maximum length of a command line (http://support.microsoft.com/kb/830473). It's recommended you make use of smaller batches if you encounter this issue.
	LoginTimeout	Gets or sets the number of seconds before the SqlCmd login to the OLE DB provider times out when you try to connect to a server. The default login time-out for SqlCmd is eight (8) seconds. The login time- out value must be a number between 0 and 65534. If the value supplied is not numeric or does not fall into that range, the SqlCmd generates an error message. A value of 0 specifies the time-out to be indefinite.
	LogOn	Gets or sets the user login id. If neither the LogOn or Password option is specified, SqlCmd tries to connect by using Microsoft Windows Authentication mode. Authentication is based on the Windows account of the user who is running SqlCmd. Note: The OSQLUSER environment variable is available for backwards compatibility. The SQLCMDUSER environment variable takes precedence over the OSQLUSER environment variable. This means that SqlCmd and osql can be used next to each other without interference.
	NewPassword	Changes the password for a user.
	OutputFile	Gets or sets the file that receives output from SqlCmd. If the UnicodeOutput option is specified, the output file is stored in Unicode format. If the file name is not valid, an error message is generated, and SqlCmd exits. SqlCmd does not support concurrent writing of multiple SqlCmd processes to the same file. The file output will be corrupted or incorrect. This file will be created if it does not exist. A file of the same name from a prior SqlCmd session will be overwritten. The file specified here is not the stdout file. If a stdout file is specified this file will not be used.
	Password	Gets or sets the user specified password. Passwords are case-sensitive. If the LogOn option is used and the Password option is not used, and the SQLCMDPASSWORD environment variable has not been set, SqlCmd uses the default password (NULL).
	QueryTimeout	Gets or sets the number of seconds before a command (or SQL statement) times out. This option sets the SqlCmd scripting variable SQLCMDSTATTIMEOUT. If a time_out value is not specified, the command does not time out. The query time_out must be a number between 1 and 65535. If the value supplied is not numeric or does not fall into that range, SqlCmd generates an error message. Note: The actual time out value may vary from the specified time_out value by several seconds.
	RedirectStandardError	Gets or sets a flag that indicates whether or not to redirect the error message output to the screen (stderr).If you do not specify a parameter or if you specify 0, only error messages that have a severity level of 11 or higher are redirected. If you specify 1, all error message output including PRINT is redirected. Has no effect if you use OutputFile. By default, messages are sent to stdout.
	Server	Gets or sets the name of the SQL Server to which to connect. It sets the SqlCmd scripting variable SQLCMDSERVER. Specify Server to connect to the default instance of SQL Server on that server computer. Specify Server to connect to a named instance of SQL Server on that server computer. If no server computer is specified, SqlCmd connects to the default instance of SQL Server on the local computer. This option is required when you execute sqlcmd from a remote computer on the network. If you do not specify a Server when you start SqlCmd, SQL Server checks for and uses the SQLCMDSERVER environment variable. Note: The OSQLSERVER environment variable has been kept for backward compatibility. The SQLCMDSERVER environment variable takes precedence over the OSQLSERVER environment variable.
	SeverityLevel	Controls the severity level that is used to set the ERRORLEVEL variable. If the ERRORLEVEL reported is >= SeverityLevel then the task will log an error.
	SqlCmdPath	Gets or sets the path to the sqlcmd.exe.
	UnicodeOutput	Gets or sets a flag that indicates if the OutputFile is stored in Unicode format, regardless of the format of the InputFiles.
	UseClientRegionalSettings	Gets or sets a flag that indicates if the SQL Server OLE DB provider uses the client regional settings when it converts currency, and date and time data to character data. The default is server regional settings.
	Variables	Creates a SqlCmd scripting variable that can be used in a SqlCmd script. You can specify multiple Variables and values. If there are errors in any of the values specified, SqlCmd generates an error message and then exits.
	Workstation	Gets or sets the workstation name. This option sets the SqlCmd scripting variable SQLCMDWORKSTATION. The workstation name is listed in the hostname column of the sys.processes catalog view and can be returned using the stored procedure sp_who. If this option is not specified, the default is the current computer name. This name can be used to identify different sqlcmd sessions.

Name

Description

CommandLineQuery

Gets or sets one or more command line queries to execute when SqlCmd starts, but does not exit sqlcmd when the query has finished running.

Database

Gets or sets the name of the database to connect to. Issues a

Copy

USE

db_name statement when you start SqlCmd. This option sets the SqlCmd scripting variable SQLCMDDBNAME. This specifies the initial database. The default is your login's default-database property. If the database does not exist, an error message is generated and SqlCmd exits.

DedicatedAdminConnection

Gets or sets a flag that indicates if the connection to SQL Server should use a Dedicated Administrator Connection (DAC). This kind of connection is used to troubleshoot a server. This will only work with server computers that support DAC. If DAC is not available, SqlCmd generates an error message and then exits. For more information about DAC, see Using a Dedicated Administrator Connection.

DisableVariableSubstitution

Causes SqlCmd to ignore scripting variables. This is useful when a script contains many INSERT statements that may contain strings that have the same format as regular variables, such as $(variable_name).

EchoInput

Gets or sets a flag that indicates if the input scripts are written to the standard output device (stdout).

EnableQuotedIdentifiers

Gets or sets a flag that sets the

Copy

SET QUOTED_IDENTIFIER

connection option to

Copy

ON

. By default, it is set to

Copy

OFF

. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

Headers

Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. This option sets the sqlcmd scripting variable SQLCMDHEADERS. Use -1 to specify that headers must not be printed. Any value that is not valid causes SqlCmd to generate an error message and then exit.

InputFiles

Gets or sets the path to a file that contains a batch of SQL statements. Multiple files may be specified that will be read and processed in order. Do not use any spaces between the file names. SqlCmd will first check to see whether all files exist. If one or more files do not exist, SqlCmd will exit. The InputFiles and CommandLineQuery options are mutually exclusive.

Please note that if you provide a large number of files, you may exceed the maximum length of a command line (http://support.microsoft.com/kb/830473). It's recommended you make use of smaller batches if you encounter this issue.

LoginTimeout

Gets or sets the number of seconds before the SqlCmd login to the OLE DB provider times out when you try to connect to a server. The default login time-out for SqlCmd is eight (8) seconds. The login time- out value must be a number between 0 and 65534. If the value supplied is not numeric or does not fall into that range, the SqlCmd generates an error message. A value of 0 specifies the time-out to be indefinite.

LogOn

Gets or sets the user login id. If neither the LogOn or Password option is specified, SqlCmd tries to connect by using Microsoft Windows Authentication mode. Authentication is based on the Windows account of the user who is running SqlCmd.

Note: The OSQLUSER environment variable is available for backwards compatibility. The SQLCMDUSER environment variable takes precedence over the OSQLUSER environment variable. This means that SqlCmd and osql can be used next to each other without interference.

NewPassword

Changes the password for a user.

OutputFile

Gets or sets the file that receives output from SqlCmd.

If the UnicodeOutput option is specified, the output file is stored in Unicode format. If the file name is not valid, an error message is generated, and SqlCmd exits. SqlCmd does not support concurrent writing of multiple SqlCmd processes to the same file. The file output will be corrupted or incorrect. This file will be created if it does not exist. A file of the same name from a prior SqlCmd session will be overwritten. The file specified here is not the stdout file. If a stdout file is specified this file will not be used.

Password

Gets or sets the user specified password. Passwords are case-sensitive. If the LogOn option is used and the Password option is not used, and the SQLCMDPASSWORD environment variable has not been set, SqlCmd uses the default password (NULL).

QueryTimeout

Gets or sets the number of seconds before a command (or SQL statement) times out. This option sets the SqlCmd scripting variable SQLCMDSTATTIMEOUT. If a time_out value is not specified, the command does not time out. The query time_out must be a number between 1 and 65535. If the value supplied is not numeric or does not fall into that range, SqlCmd generates an error message.

Note: The actual time out value may vary from the specified time_out value by several seconds.

RedirectStandardError

Gets or sets a flag that indicates whether or not to redirect the error message output to the screen (stderr).If you do not specify a parameter or if you specify 0, only error messages that have a severity level of 11 or higher are redirected. If you specify 1, all error message output including PRINT is redirected. Has no effect if you use OutputFile. By default, messages are sent to stdout.

Server

Gets or sets the name of the SQL Server to which to connect. It sets the SqlCmd scripting variable SQLCMDSERVER.

Specify Server to connect to the default instance of SQL Server on that server computer. Specify Server to connect to a named instance of SQL Server on that server computer. If no server computer is specified, SqlCmd connects to the default instance of SQL Server on the local computer. This option is required when you execute sqlcmd from a remote computer on the network.

If you do not specify a Server when you start SqlCmd, SQL Server checks for and uses the SQLCMDSERVER environment variable.

Note: The OSQLSERVER environment variable has been kept for backward compatibility. The SQLCMDSERVER environment variable takes precedence over the OSQLSERVER environment variable.

SeverityLevel

Controls the severity level that is used to set the ERRORLEVEL variable. If the ERRORLEVEL reported is >= SeverityLevel then the task will log an error.

SqlCmdPath

Gets or sets the path to the sqlcmd.exe.

UnicodeOutput

Gets or sets a flag that indicates if the OutputFile is stored in Unicode format, regardless of the format of the InputFiles.

UseClientRegionalSettings

Gets or sets a flag that indicates if the SQL Server OLE DB provider uses the client regional settings when it converts currency, and date and time data to character data. The default is server regional settings.

Variables

Creates a SqlCmd scripting variable that can be used in a SqlCmd script. You can specify multiple Variables and values. If there are errors in any of the values specified, SqlCmd generates an error message and then exits.

Workstation

Gets or sets the workstation name. This option sets the SqlCmd scripting variable SQLCMDWORKSTATION. The workstation name is listed in the hostname column of the sys.processes catalog view and can be returned using the stored procedure sp_who. If this option is not specified, the default is the current computer name. This name can be used to identify different sqlcmd sessions.

MSBuild Extension Pack

Reference

Get in touch