Part one of this two part series has introduced tools hidden in every SQL Server installation, with the primary focus being an introduction to the SQL Server command line utilities. The next installment will continue our examination of these utilities. Acceptable Use Policy. Advertiser Disclosure:. SQL Etc. Database Forum. Rss Feed. MS Access. Free Newsletters: Email subscribe. Advertiser Disclosure. August 17th, AM. Need help changing table contents.
MS SQL. This article is a continuation of " Microsoft SQL Server command line utilities Part 1 ," which began our discussion of how to use the Microsoft SQL Server command line utilities, and what are they used for. Most of these utilities are primarily used for diagnostic, configuration and data migration purposes, rather than for database server administration. Queue Reader Agent is used with snapshot and transactional publications that allow queued updating.
Readpipe - Readpipe is a diagnostic utility used for the network Named Pipe services "checks-if-it-is-working" integrity testing. Readpipe used in conjunction with Makepipe utility. Rebuildm - The Rebuildm utility is used for 1 fixing a corrupt master database, and 2 changing the character set, sort order or Unicode collation for a Microsoft SQL Server installation. As usually, make sure that you have a valid backup s for all user databases. After rebuilding master, you need to reload all your data user databases from a backup s you have.
Replmerg - The Replication Merge Agent utility that configures and starts a replication Merge Agent, which applies initial snapshots held in the database tables of the Publisher to the Subscribers. Clean output improves the processing performance of scripting languages.
The following display is an example of the format for performance statistics:. If the optional parameter 1 is specified, the output format of the statistics is in colon-separated format that can be imported easily into a spreadsheet or processed by a script.
If the optional parameter is any value other than 1 , an error is generated and sqlcmd exits. The disabled commands are still recognized; sqlcmd issues a warning message and continues. If the optional parameter 1 is specified, sqlcmd generates an error message and then exits.
The following commands are disabled when the -X option is used:. If the -X option is specified, it prevents environment variables from being passed on to sqlcmd. For more information about sqlcmd scripting variables, see Use sqlcmd with Scripting Variables. Displays the version of sqlcmd and a syntax summary of sqlcmd options. When multiple results are returned, sqlcmd prints a blank line between each result set in a batch.
To use sqlcmd interactively, type sqlcmd at the command prompt with any one or more of the options described earlier in this article. For more information, see Use the sqlcmd Utility. The options -L , -Q , -Z or -i cause sqlcmd to exit after execution. The total length of the sqlcmd command-line in the command environment Cmd.
To view the environmental variables, in Control Panel , open System , and then click the Advanced tab. In addition to Transact-SQL statements within sqlcmd , the following commands are also available:. To maintain backward compatibility with existing osql scripts, some of the commands will be recognized without the colon, indicated by the [ : ]. Each command must be on a separate line. A command cannot be followed by a Transact-SQL statement or another command.
Commands are executed immediately. They are not put in the execution buffer as Transact-SQL statements are. Editing Commands [ : ] ED Starts the text editor. This editor can be used to edit the current Transact-SQL batch, or the last executed batch.
To edit the last executed batch, the ED command must be typed immediately after the last batch has completed execution. The default editor is 'Edit'. For example, to set the editor to Microsoft Notepad, at the command prompt, type:.
Implicitly using a command-line option. The -X option prevents environment variables from being passed on to sqlcmd. If a variable defined by using :Setvar and an environment variable have the same name, the variable defined by using :Setvar takes precedence.
If the string value of the scripting variable contains blank spaces, enclose the value in quotation marks. If a value for a scripting variable is not specified, the scripting variable is dropped. Only scripting variables that are set by sqlcmd , and those that are set using the :Setvar command will be displayed.
The Error command can appear multiple times in a script. By default, error output is sent to stderr. If the file already exists, it will be truncated to zero bytes. If the file is not available because of permissions or other reasons, the output will not be switched and will be sent to the last specified or default destination.
If this has been redirected, the target to which the stream has been redirected will receive the error output. By default, output is sent to stdout. If the file already exists, it is truncated to zero bytes. The Out command can appear multiple times in a script.
By default performance trace output is sent to stdout. The Perftrace command can appear multiple times in a script. Execution Control Commands :On Error [ exit ignore ] Sets the action to be performed when an error occurs during script or batch execution. When the exit option is used, sqlcmd exits with the appropriate error value.
When the ignore option is used, sqlcmd ignores the error and continues executing the batch or script. By default, an error message is printed. If numeric, the first column of the last result row is converted to a 4-byte integer long. Windows x passes the whole 4-byte integer. The syntax is:. You can also include the EXIT parameter as part of a batch file. For example, at the command prompt, type:.
The sqlcmd utility sends everything between the parentheses to the server. If a system stored procedure selects a set and returns a value, only the selection is returned. Executes the batch that includes the query, and then quits after it returns the results of the query. For example:.
This error will cause the sqlcmd script to end and return the message ID to the client. The return values -1 to are reserved by SQL Server, and sqlcmd defines the following additional return values:.
The batch is executed multiple times as separate batches. You cannot declare a variable more than once in a single batch. The file will be read and executed after a batch terminator is encountered.
You can issue multiple :r commands. The file may include any sqlcmd command. This includes the batch terminator GO. The line count that is displayed in interactive mode will be increased by one for every :r command encountered.
The :r command will appear in the output of the list command. Also closes the current connection. If neither options nor environment variables are provided, Windows Authentication mode is used to sign in.
For example to connect to an instance, instance1 , of SQL Server, myserver , by using integrated security you would use the following command:. To connect to the default instance of myserver using scripting variables, you would use the following:. To execute an operating system command, start a line with two exclamation marks!! Output files can be specified with the -o option or the :Error , :Out and :Perftrace commands.
The following are some guidelines for working with these files:. The output file is created on the local computer and not on the remote server. If there is a space in the path, use quotation marks. In the following example, after the Transact-SQL statements are executed, an informational message is printed.
By default, this is a space. If the column name is shorter than the column width, the output is padded with spaces up to the next column. This line will be followed by a separator line that is a series of dash characters. The following output shows an example. Although the BusinessEntityID column is only four characters wide, it has been expanded to accommodate the longer column name.
0コメント