Return to the command line tools of SQL Server (2)-sqlcmd.exe (Part 1)

After learning about the sqlcmd parameter, let's take a look at the sqlcmd command and use: HelpLet's take a look at the common commands.

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, sometimes I forget (or I am lazy trying to copy) the IO file name.ProgramProvides the Dir command.

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 ents and Settings \ chenxie. 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 isEdit.com. You can modifySqlcmdeditorEnvironment variables to change the Editor (for exampleSet sqlcmdeditor = notepad).

I do not know why.Edit.comThen return to sqlcmd. All the Chinese documents cannot correctly display the specific reason and cannot be found. Depressed, hope to be a master.

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