SQL Server command line tool-sqlcmd and so on ..

As usual, we first use /? Let's take a look at the startup parameters of sqlcmd:

 

C: documents and settingschenxie. iflytek> sql0000.exe /?

Microsoft (r) SQL server command line tool

Version 9.00.1399.06 nt Intel x86

Copyright (c) Microsoft Corporation. All rights reserved.

 

Usage: sqlcmd [-u logon id] [-P Password]

[-S server] [-H host name] [-e trusted connection]

[-D use the database name] [-l logon timeout value] [-T query timeout value]

[-H title] [-s column separator] [-W screen width]

[-A packet size] [-e echo input] [-I allows a quoted identifier]

[-C command end] [-L [c] list servers [clear output]

[-Q "command line query"] [-Q "command line query" and exit]

[-M error level] [-V severity level] [-W deleting trailing spaces]

[-U Unicode output] [-R [0 | 1] Message sent to stderr]

[-I input file] [-O output file] [-Z New Password]

[-F <code page> | I: <code page> [, O: <code page>] [-Z: Create a password and exit]

[-K [1 | 2] Delete [Replace] control character]

[-Y variable length type display width]

[-Y fixed length type display width]

[-P [1] print statistics [colon format]

[-R use client region settings]

[-B abort batch processing when an error occurs]

[-V variable = "value"...] [-a dedicated management connection]

[-X [1] disabling commands, startup scripts, and environment variables [and exiting]

[-X variable disabling]

[-? Show syntax Summary]

 

Haha, there is no change with osql. Next I will explain each parameter and their usage carefully.

 

 

 

The following is a detailed explanation of these parameters (Note: Case Sensitive ):

1. First, login:

If-u-p-S is empty, for example, if you directly type "sql1_" or" sql1_.exe "in cmd, the local account will be used to try to connect to the local database.

-U login_id

User Logon ID. The logon ID is case sensitive.

If the-u option and-P option are not specified, sqlcmd attempts to connect in Microsoft Windows Authentication mode. Authentication is based on the Windows Account of the user running sqlcmd.

If the-u option is used with the-E Option (described later in this topic), an error message is generated. If the-u option has multiple parameters, an error message is generated and the program is exited.

 

-P Password

Password specified by the user. The password is case sensitive. If the-P option is not used and the sql1_password environment variable is not set, sqlcmd prompts the user to enter the password. If you use the-P option at the end of the command prompt without a password, sqlcmd uses the default password (null ).

By printing the password prompt to the console, you can display the password prompt as follows:

Password:

Hide user input, which means no input content is displayed and the cursor remains unchanged.

If you use the-P option with the-e option, an error message is generated.

If the-P option has multiple parameters, an error message is generated and the program is exited.

-E

Log on to SQL Server using a trusted connection instead of a user name and password. By default, sqlcmd uses the trusted connection option.

-E Option ignores possible user name and password environment variable settings, such as sql1_password. If you use the-e option with the-u option or-P option, an error message is generated.

-S SERVER_NAME [instance_name]

The instance of the SQL Server to be connected. It sets the sqlcmd script variable sqlcmd Server

Specify SERVER_NAME to connect to the default instance of SQL Server on this server. Specify server_nameinstance_name to connect to the name instance of SQL Server on this server. If no server 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 no user name is specified when sqlcmd is started, SQL Server checks and uses the sqlbench server environment variable. If no server is specified, the workstation name is used.

-H wksta_name

The name of the workstation. This option sets the sqlcmd script variable sql1_workstation. The workstation name is listed in the hostname column of the SYS. processes directory view and can be returned using the Stored Procedure sp_who. If this option is not specified, the current computer name is used. This name can be used to identify different sqlcmd sessions.

 

2. connection settings:

 

-L time_out

Specifies the logon timeout time from sqlcmd to the ole db access interface. This option sets the sqlcmd script variable sql1_logintimeout. The default timeout time for logging on to sqlcmd is 8 seconds. The logon timeout value must be a number between 0 and 65534. If the value provided is not a value or is not in this range, sqlcmd generates an error message. If the value is 0, unlimited waiting is allowed.

-T time_out

Specifies the timeout time of a command (or SQL statement. This option sets the sqlcmd script variable sql1_stattimeout. If the time_out value is not specified, the command will not time out. Logon time_out must be a number between 1 and 65535. If the value is not a value or is not in this range, sqlcmd generates an error message.

 

Note: The actual timeout value may be several seconds different from the specified time_out value. Several seconds, not several milliseconds.

 

-D db_name

When sqlcmd is started, a use db_name statement is issued. This option sets the sqlcmd script variable sqlmongodbname. It specifies the initial database. If the database does not exist, an error message is generated and sqlcmd exits.

 

-

Log on to SQL Server using a dedicated administrator connection (DAC. This type of connection is used to eliminate server faults. This only applies to servers that support DAC. If the DAC is unavailable, sqlcmd generates an error message and exits. Very good things, which will be described in detail below.

 

 

-I

Set the set quoted_identifier connection option to on. By default, it is set to off. This is different from the default setting of SQL Server, where SQL is set to on by default.

 

Posts:Quoted_identifier, Delimiter settings.

When set quoted_identifier is on (default), all strings separated by double quotation marks are interpreted as object identifiers. (For example, select * from [user], or select * from "user", here the user is considered as a non-system reserved word)

Therefore, the quoted identifiers do not have to comply with the rules for Transact-SQL identifiers. They can be reserved keywords and contain characters that are generally not allowed in the transact-SQL identifier. Character string expressions cannot be separated by double quotation marks, but must be enclosed by single quotation marks. If single quotes (') are part of a text string, they can be expressed by two single quotes. When reserved keywords are used for object names in the database, set quoted_identifier must be on.

When set quoted_identifier is off, the text strings in the expression can be separated by single quotation marks or double quotation marks. If the text string is separated by double quotation marks, it can contain embedded single quotation marks, such as ellipsis.

For details about SQL operations, the default value is on or off. Please refer to the online manual of sqlserver2005, which is described in detail.

 

 

 

3. Display Settings:

 

-H Headers

Specifies the number of rows to be printed between column headers. By default, the system prints a title for each query result group. This option sets the sqlcmd script variable sql1_headers. Use-1 to specify that the title cannot be printed. If-1 is used, there is no space between the parameter and the setting (it can be-h-1, but not-h-1 ). Any invalid value will cause sqlcmd to generate an error message and exit.

 

-W column_width

 

Specifies the screen width for output. This option sets sqlcmd script variable sql1_colwidth. The column width must be a number between 8 and 65536. If the specified column width is not within this range, sqlcmd generates an error message. The default width is 80 characters. When the output row exceeds the specified column width, It is redirected to the next row.

-E

Print the input script to the standard output device (stdout ).

-R [0 | 1]

Redirects the error message output to the screen (stderr ). If no parameter is specified or the specified parameter is 0, only 17 or higher error messages are redirected. If the parameter is set to 1, all messages (including print) will be redirected ).

-K [1 | 2]

Delete all control characters in the output, such as tabs and line breaks. This will retain the column format when returning data. If 1 is specified, the control character is replaced by a space. If 2 is specified, a consecutive control character is replaced by a space.

-P [1]

Print the performance statistics for each result set. The following example shows the Performance Statistics format:

Network packet size (bytes): n X xact [s]: Clock Time (Ms.): Total T1 AVG T2 (T3 xacts per sec .)

Where

X = the number of transactions processed by SQL Server.

T1 = total time of all transactions.

T2 = average time of a single transaction.

T3 = the average number of transactions per second.

All times are expressed in milliseconds.

If the optional parameter 1 is specified, the output format of the statistical information is in the colon-separated format, which can be easily imported into a workbook or processed by a script.

If the optional parameter is any value other than 1, an error is generated and sqlcmd exits.

-O output_file

Identifies the file that receives output from sqlcmd.

If-u is specified, output_file is stored in unicode format. The file name containing spaces must be enclosed in quotation marks. If the file name is invalid, 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 is corrupted or incorrect.

 

 

-U

Specify the formats regardless of input_file. output_file is stored in unicode format.

 

4. query Configuration:

-Q "query"

When sqlcmd is started, the query is executed, but sqlcmd is not exited when the query is complete. Enclose the query in quotation marks, as shown in the following example. (We recommend that you do not use this option. If you want to write a batch processing command or automatically execute the program, use the following one)

At the command prompt, type (do not use the go Terminator in the query ):

Sqlcmd-D adventureworks-Q "select firstname, lastname from person. Contact where lastname like 'whi % '"

If-B is specified with this option, sqlcmd exits when an error occurs.

-Q "query"

Execute the query and immediately exit sqlcmd. (We recommend that you do not use this unless you write a batch processing command or automatically execute the program)

If-B is specified with this option, sqlcmd exits when an error occurs.

 

-I input_file [, input_file2...]

Identifies a file that contains a batch of SQL statements or stored procedures. You can specify multiple files to be read and processed in order. Do not use any space between file names. Sqlcmd first checks whether all specified files exist. If one or more files do not exist, sqlcmd exits.

 

-B

When an error occurs, sqlcmd exits and returns a DOS errorlevel value. When the Severity Level of the SQL server error message is higher than 10, the value returned to the DOS errorlevel variable is 1; otherwise, the returned value is 0. If the-V option is set in addition to the-B option, sqlcmd does not report an error when the severity level is lower than the value set using-v. Run the command prompt to test the errorlevel value of the batch file and handle the error as appropriate. Sqlcmd does not report an error (information message) for severity 10 ).

If the sqlcmd script contains incorrect comments, syntax errors, or missing script variables, the returned errorlevel is 1.

 

-V

Specifies the minimum Severity Level of the sqlcmd report. When an error occurs in a Transact-SQL script, the severity level is reported only when the severity level is greater than or equal to the value specified by the-V Switch. If the severity level is lower than this value, 0 is reported. Run the command prompt to test the errorlevel value of the batch file and handle the error as appropriate.

 

-S col_separator

Specifies the column delimiter. The default value is space. This option sets the sqlcmd script variable sql1_colsep. To use a character (such as and (&) or semicolon (;) that has a special meaning on the operating system), use double quotation marks. The column separator can be any 8 characters.

 

-C _ end

Specifies the batch processing Terminator. By default, the command is terminated and sent to SQL Server by typing "go" in a single line. When resetting the batch Terminator, do not retain keywords or characters that have special significance for the operating system, even if there is a backslash before them.

 

-R

Set the SQL Server OLE DB access interface to use the client region when converting the currency, date, and time data to character data.

 

5. parameter settings:

 

-A packet_size

Data packets of different sizes are required. This option sets the sqlcmd script variable sqlcmdpacketsize. Packet_size must be a value between 512 and 32767. If the two go commands of the script contain a large number of SQL statements, using large data packets can improve the performance of script execution. We can request larger data packets, but if the request is rejected, sqlcmd will default the server's default data packet size.

 

-L [c]

Lists the names of the Servers configured locally and the servers broadcasted on the network. This parameter cannot be used with other parameters. The maximum number of servers that can be listed is 3000. If the server list is truncated due to the buffer size, an error message is displayed.

If the optional parameter C is specified, the output will not display servers: header line, and each server line listed has no leading space. This is called clearing output. Clearing output can improve the processing performance of the script language.

Note: Due to the nature of network broadcast, sqlcmd may not be able to receive responses from all servers in a timely manner. Therefore, each time you call this option, the list of returned servers may be different based on different characters. :-)

 

-M error_level

Display custom error messages. Displays the message numbers, status, and error levels of errors higher than the specified severity level. The system does not display error messages with a severity level lower than the specified severity level. Use-1 to specify that all headers are returned together with messages, even information messages. If-1 is specified, no space is allowed between the parameter and the setting (for example, it can be m-1, but not-m-1 ).

This option sets the sqlcmd script variable sqlcmderrorlevel.

-W

This option deletes trailing spaces of a column. When preparing data to be exported to another application, use this option with the-s option. It cannot be used with the-y or-Y option.

 

-F <codePage> | I: <codePage> [<, O: <codePage>]

Specifies the input and output code pages. The code page number is the value of the installed Windows code page.

-Y display_width

The maximum number of characters returned by a variable-length data type is:

· Varchar (max)

· Nvarchar (max)

· Varbinary (max)

· XML

· UDT (user-defined data type)

· Text

· Ntext

· Image

According to the implementation, the UDT can use a fixed length. If the length of the fixed UDT is shorter than display_width, the returned UDT value will not be affected. However, if the length is longer than display_width, the output will be truncated. If display_width is 0, the output will be truncated at 1 MB.

Pay special attention when using the-Y 0 option, because this option may cause serious performance problems on the server and network based on the returned data volume.

-Y display_width

The limit is the number of characters returned for the following data types:

· Char

· Nchar

· Varchar (N), where 1 <n <8000

· Nvarchar (n) where 1 <n <4000

· SQL _variant

-V Var = "value" [Var = "value"...]

Create a sqlcmd script variable that can be used in the sqlcmd script. If the value contains a character, it is enclosed by quotation marks. You can specify multiple Var = "values" values. If an error occurs in any value, sqlcmd generates an error message and exits.

-X [1]

When executing sqlcmd from a batch file, commands that may compromise system security are disabled. The disabled command can still be identified; sqlcmd sends a warning message and continues. If parameter 1 is specified, sqlcmd generates an error message and exits. The following command is disabled when the-x option is used:

· Ed

·!! Command

If the-x option is specified, it will prevent the environment variable from being passed to sqlcmd. At the same time, this option also prevents execution of the startup script specified by using the sqlcmdini script variable.

-X

This causes sqlcmd to ignore the script variable. This option is useful when the script contains multiple insert statements and these statements may contain strings of the same format as conventional variables, such as $ (variable_name.

 

After learning about the sqlcmd parameters, let's take a look at the sqlcmd commands and use: help to see which commands are common.

1>: Help

:!! [<Command>]

-Run the command in Windows Shell.

: Connect server [instance] [-l timeout value] [-u user [-P Password

-Connect to the SQL server instance.

: Ed

-Edit the current statement cache or the statement cache executed last time.

: Error <target>

-Redirect error output to a file, stderr, or stdout.

: Exit

-Exit sqlcmd immediately.

: Exit ()

-Execute the statement cache; exit directly if no return value is returned.

: Exit (<query>)

-Execute the specified query and return numerical results.

Go [<n>]

-Executed statement cache (n times ).

: Help

-The command list is displayed.

: List

-Content cached by the output statement.

: Listvar

-List the configured sqlcmd script variables.

: On error [exit | ignore]

-Measures to handle errors when executing batch processing or sqlcmd commands.

: Out <File Name> | stderr | stdout

-Redirects query output to a file, stderr, or stdout.

: Perftrace <File Name> | stderr | stdout

-Redirect the timing output to a file, stderr, or stdout.

: Quit

-Exit sqlcmd immediately.

: R <File Name>

-Append the file content to the statement cache.

: Reset

-Discard statement cache.

: Serverlist

-List the local SQL Server and the SQL server on the network.

: Setvar {Variable}

-Delete the sqlcmd script variable.

: Setvar <variable> <value>

-Set the sqlcmd script variable.

All sqlcmd commands start with the ":" symbol (except go, The go system considers it to have added a character: Go to the cache). Although some commands can be omitted ": ", but in order to ensure security and convenience of memory, we recommend that you put it on": ", hard work, more": "Will not trigger any bloody cases, I promise! Pai_^

 

The following is an explanation of each command:

[:]! <Command>

It is common in scripts to execute operating system commands, which greatly enhances interactivity.

Of course, the Dir command is occasionally provided for programmers who forget (or are too lazy to copy) Io file names.

The following is an example

3> !! Dir

The volume in drive C is not labeled.

The serial number of the volume is 044a-c8d9.

C: documents and settingschenxie. iflytek directory

<Dir>.

<Dir> ..

0 AWStats. myvirtualhostname

0 awstats.myvirtualhostname.html

<Dir> favorites

664 intlname. OLS

<Dir> my documents

<Dir> vswebcache

<Dir> Windows

<Dir> Start Menu

<Dir> Desktop

3 files in 664 bytes

7,063,961,600 bytes available for 8 Directories

: Connect SERVER_NAME [instance_name] [-l timeout] [-u user_name [-P Password]

-Close the current connection and connect to another SQL server instance. If no timeout is specified, the sqlcmdlogintimeout variable value is used.

: Ed

-Edit the current statement cache or the statement cache executed last time.

Summon editor to edit the statements in the cache. The default value is edit.com. You can change the editor by modifying the sqlcmdeditor environment variable (for example, set sqlcmdeditor = notepad ).

Another sentence: I don't know why. I returned to sqlcmd after using edit.com. All the Chinese documents cannot correctly display the specific reason, and I am very depressed.

: Error <FILENAME> | stderr | stdout

-Redirect error output to a file.

If the file already exists, it is truncated to zero bytes. If the file is not accessible (for permission reasons or other reasons), the output will not be switched or sent to the last specified target or default target. By default, the error output is sent to the stderr stream.

: Exit [(statement)]

-Exit sqlcmd immediately. : Exit usage is very exquisite. Pay attention to it when using it.

: Exit
Exit immediately without batch processing. No return value is returned.

: Exit ()
Exit after batch processing. No return value is returned.

: Exit (query)
Execute the batch processing that includes the query, return the query result, and exit.

The statement value is to convert the first column of the First result row to a 4-byte INTEGER (long integer ). SQL Server retains the return values between-1 and-99. sqlcmd defines the following additional return values-100: errors encountered before selecting the return values. -101: the row cannot be found when the return value is selected. -102: A Conversion error occurs when the return value is selected.

When writing an automatic Script: exit is very practical.

Go [<n>]

-Executed statement cache (n times ). Example:

1> select count (*) from testtab;
2> go 3
-----------
410
(One row is affected)
-----------
410
(One row is affected)
-----------
410
(One row is affected)
1>

: List

-Content cached by the output statement. After go is used, the statement is cleared. The list is empty. Example:

1> select count (*) from testtab;
2>: List
Select count (*) from lcs_bizobject;

2> go
-----------
410
(One row is affected)
1>: List
1>

: Listvar

-List the configured sqlcmd script variables to facilitate the use of sqlcmd.

: On error [exit | ignore]

-Measures to handle errors when executing batch processing or sqlcmd commands.
Set the operations to be performed when an error occurs during script or batch processing.
By default, an error message is printed.

Exit: sqlcmd exits and displays the corresponding error value.
Ignore: sqlcmd ignores errors and continues to execute batch processing or scripts.

: Out <FILENAME> | stderr | stdout

-Redirect all query results to the file, stderr, or stdout specified by filename.
By default, the output is sent to stdout.

: Perftrace <FILENAME> | stderr | stdout

-Redirect the timing output to a file, stderr, or stdout.
By default, the output is sent to stdout.

: Quit

-Exit sqlcmd immediately.

: R <FILENAME>

-Append the file content to the statement cache.

: Reset

-Discard statement cache.

: Serverlist

-List SQL servers on the network.

: Setvar {Variable}

-Delete the sqlcmd script variable.

: Setvar <variable> <value>

-Set the sqlcmd script variable.

If the name of a variable defined by setvar is the same as that of an environment variable, the variable defined by setvar takes precedence. The variable name cannot contain space characters. If the string value of the script variable contains spaces, enclose the value in quotation marks. Otherwise, they will be ignored.
If the script variable value is not specified, the script variable is deleted.
The variable name cannot be in the same form as the variable expression (for example, $ (VAR ).