SqlCmd Class

MSBuild Extension Pack

previous page next page
SqlCmd Class MSBuild Extension Pack Help 4.0.12.0
Wraps the SQL Server command line executable SqlCmd.exe.

Valid TaskActions are:

Execute (Required: CommandLineQuery or InputFiles Optional: Database, DedicatedAdminConnection, DisableVariableSubstitution, EchoInput, EnableQuotedIdentifiers, Headers, LoginTimeout, LogOn, NewPassword, OutputFile, Password, QueryTimeout, RedirectStandardError, Server, SeverityLevel, SqlCmdPath, UnicodeOutput, UseClientRegionalSettings, Variables, Workstation)

Remote Execution Support: Yes

Inheritance Hierarchy
SystemObject  Microsoft.Build.UtilitiesTask
    MSBuild.ExtensionPackBaseTask
      MSBuild.ExtensionPack.SqlServerSqlCmd

Namespace: MSBuild.ExtensionPack.SqlServer
Assembly: MSBuild.ExtensionPack (in MSBuild.ExtensionPack.dll) Version: 4.0.0.0

The SqlCmd type exposes the following members.

Constructors
  NameDescription
Public methodSqlCmd
Initializes a new instance of the SqlCmd class
Top
Properties
  NameDescription
Public propertyCommandLineQuery
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.
Public propertyDatabase
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.
Public propertyDedicatedAdminConnection
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.
Public propertyDisableVariableSubstitution
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).
Public propertyEchoInput
Gets or sets a flag that indicates if the input scripts are written to the standard output device (stdout).
Public propertyEnableQuotedIdentifiers
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).
Public propertyHeaders
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.
Public propertyInputFiles

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.
Public propertyLoginTimeout
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.
Public propertyLogOn

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.

Public propertyNewPassword
Changes the password for a user.
Public propertyOutputFile

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.

Public propertyPassword

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

Public propertyQueryTimeout

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.

Public propertyRedirectStandardError
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.
Public propertyServer

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.

Public propertySeverityLevel
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.
Public propertySqlCmdPath
Gets or sets the path to the sqlcmd.exe.
Public propertyUnicodeOutput
Gets or sets a flag that indicates if the OutputFile is stored in Unicode format, regardless of the format of the InputFiles.
Public propertyUseClientRegionalSettings
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.
Public propertyVariables
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.
Public propertyWorkstation
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.
Top
Examples
Copy
 <Project ToolsVersion="4.0" DefaultTargets="Default" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <TPath>$(MSBuildProjectDirectory)\..\MSBuild.ExtensionPack.tasks</TPath>
        <TPath Condition="Exists('$(MSBuildProjectDirectory)\..\..\Common\MSBuild.ExtensionPack.tasks')">$(MSBuildProjectDirectory)\..\..\Common\MSBuild.ExtensionPack.tasks</TPath>
    </PropertyGroup>
    <Import Project="$(TPath)"/>
    <ItemGroup>
        <InputFile Include="C:\File1.sql"/>
        <InputFile Include="C:\File2.sql"/>
        <InputFile Include="C:\File3.sql"/>
    </ItemGroup>
    <ItemGroup>
        <Variable Include="DbName">
            <Value>master</Value>
        </Variable>
    </ItemGroup>
    <Target Name="Default">
        <!-- Simple CommandLineQuery -->
        <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" CommandLineQuery="SELECT @@VERSION;" />
        <!-- Simple CommandLineQuery setting the Server and Database and outputing to a file -->
        <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="@(DbName)" CommandLineQuery="SELECT @@VERSION;" OutputFile="C:\Output.txt"/>
        <!-- Simple CommandLineQuery setting the Server and Database and running external files -->
        <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="@(DbName)" InputFiles="@(InputFile)" />
        <!-- Simple CommandLineQuery setting the Server and Database, running external files and using variable substition -->
        <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="@(DbName)" InputFiles="@(InputFile)" Variables="@(Variable)" />
    </Target>
</Project>
See Also

Reference

MSBuild.ExtensionPack.SqlServer Namespace
previous page start next page