isql实用工具(ISQLutility)
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
isql 实用工具(ISQL utility)
ISQL utility
The ISQL utility ISQL utility enables you to input Transact-SQL statements, system processes, and script files; and uses
DB-Library to communicate with Microsoft SQL Server "2000".
Syntax ISQL
Lists the servers configured locally and the name of the server that broadcasts on the network.
-U login_id
User login ID. Login ID case sensitive.
-P password
Is the user specified password. If you don't use the -P option, ISQL prompts you to enter the password. If you use the -P option without the password at the end of the command prompt, the ISQL uses the default password (NULL). Password case sensitive.
The ISQLPASSWORD environment variable allows you to set the default password for the current session. Therefore, no hard code is required to set the password in the batch file.
If you do not specify a password for the -P option, ISQL first checks the ISQLPASSWORD variable. If no value is set, ISQL uses the default password (NULL). The following example sets the ISQLPASSWORD variable at the command prompt, and then accesses
the ISQL utility:
C:\>SET ISQLPASSWORD=abracadabraC:\>isql
-E
Use trusted connections instead of requesting passwords.
-S server_name
Specifies the default instance of the SQL Server to connect to. ISQL does not support linking to SQL Server 2000 named instances. If no server is specified, ISQL will connect to the default instance of the SQL Server on the local computer. This option is required if you want to execute ISQL from a remote computer on the network.
-H wksta_name
Workstation name. The workstation name is stored in sysprocesses.hostname and displayed by sp_who. If not specified, the current computer name is used.
-d db_name
Issue a USE db_name statement when starting isql.
-l time_out
The number of seconds before the ISQL logon timeout is specified. If the time_out value is not specified, the command is run
indefinitely. The default timeout to logon to ISQL is 8 seconds.
-t time_out
The number of seconds before the specified command timeout. If the time_out value is not specified, the command will run indefinitely; the default timeout to log in to ISQL is 8 seconds.
-h headers
Specifies the number of rows to be printed between column headings. The default is to print a title for each query result set. Use - 1 specifies not to print titles. If you use - 1, there is no space between parameters and settings (-h-1, not -h –1).
-s col_separator
Specify column separator
Character, its default is blank. If you want to use a special meaning to the operating system (such as the character |; & < >), please have the characters in double quotes (") quoted.
-w column_width
Allows users to set the width of screen output. The default is 80 characters. When the output line reaches its maximum screen width, it splits into multiple rows.
-a packet_size
Enables you to request packets of different sizes. The effective value of packet_size is between 512 and 65535. The default value of ISQL in Microsoft Windows NT version is 8192; in addition, the default value of ISQL in Microsoft MS-DOS version is 512, but in this version you can request larger data packets. The increase of packet size can improve the performance of larger script execution. In this execution, the number of SQL statements between GO commands is very important. The test of Microsoft shows that 8192 is the typical fastest setting of bulk copy operation. A larger packet size can be requested, but if the request cannot be approved, the ISQL defaults to 512.
-e
Return input.
-x max_text_size
Specifies the maximum length of return text data, in bytes. Text values longer than max_text_size will be truncated.
If no max_text_size is specified, the text data is truncated at 4096 bytes.
-c cmd_end
Specifies the command terminator. By default, the is terminated by sending GO in a single line and sent to SQL Server 2000. When
resetting the command terminator, do not use the Transact-SQL reserved word or character that has special meaning to the operating system, whether or not it has a backslash.
-q "query""
When ISQL is started, the query is executed, but the ISQL is not exited when the query is completed. (note that query statements should not contain GO). If you send a query from a batch file, you can use%variables. The environment%variables% is also available. For example:
SET table = sysobjectsisql /q "Select * from%table%""
The query is enclosed in double quotation marks, and any content embedded in the query is enclosed in single quotation marks.
-Q "query""
Execute query and exit ISQL immediately when the query completes. The query is enclosed in double quotation marks, and any content embedded in the query is enclosed in single quotation marks.
-n
Delete numbering and prompt symbols from the input line (>).
-m error_level
Display of custom error information. Displays the number,
status, and error level of the specified or higher severity level errors. Any information that does not display a severity level below the specified level. -1 specifies that all headers are returned with the message, even the message of the information class. If you use - 1, there is no space between parameters and settings (-m-1, not -m -1).
-r {0 1} |
Redirect the message output to the screen (stderr). If the parameter is not specified, or the specified parameter is 0, then the only directional severity level of 17 or higher error information. If the specified parameter is 1, all message output (including "print") will be redirected.
-i input_file
Identifies a file that contains a batch of SQL statements or stored procedures. Less than (<) comparison operators can be used instead of - I.
-o output_file
Identifies files that receive output from the isql. Larger than (>) comparison operators can be used instead of - O.
-p
Print performance statistics.
-b
When the specified error occurs, ISQL exits and returns a DOS ERRORLEVEL value. When the severity level of SQL Server error information is 10 or higher, the value returned to the DOS ERRORLEVEL variable is 1; otherwise, the return value is 0. The MS-DOS batch file can test the value of DOS ERRORLEVEL and handle errors appropriately.
-O
The behavior of specifying ISQL to revert to earlier versions. The following functions are disabled:
EOF batch processing
Automatic adjustment of console width
Wide information
This option also sets the default value of DOS ERRORLEVEL to - 1.
Notes
All DB-Library applications (such as ISQL) connect to SQL Server 2000 and work as a SQL Server 6.5 level client. They do not support some of the functions of SQL Server 2000. The OSQL utility is based on ODBC and supports all the functions of SQL Server 2000. Using OSQL to run scripts that ISQL cannot run. For more information on the SQL Server 6.5 level client limit, see the early version client connection to SQL Server 2000 in
the SQL Server online book.
By default, the SQL query analyzer saves the SQL script as a Unicode file. The ISQL utility does not support the Unicode input file. Attempting to specify one of these files in the -i switch causes error 170:
Incorrect syntax near ''
Run these Unicode files using the OSQL utility. Another method is to specify ANSI instead of Unicode in the file format list of the file / Save As dialog box in the SQL query analyzer.
Like most DB-Library applications, the ISQL utility does not set any connection options by default. If a user wishes to use a specific connection option setting, the SET statement must be sent interactively or in a script.
The ISQL utility starts directly from the operating system and uses the case - sensitive options listed in this article. After startup, ISQL accepts the Transact-SQL statement and sends them interactively to SQL Server 2000. The result will be formatted and printed to the standard output device (screen). You can exit ISQL using QUIT or EXIT.
If the username is not specified when the ISQL is started, SQL Server 2000 checks the environment variables and uses them, such as isqluser= (user) or isqlserver= (server). If the environment variable is not set, the workstation user name is used. If the server is not specified, the workstation name is used.
If -U or -P options are not used, SQL Server 2000 tries to connect with Windows authentication mode. Authentication is based on the user's Windows NT account running isql.
In addition to the Transact-SQL statements used in ISQL, the commands in the following table are also available.
Command description
All statements entered after the last GO command is executed by GO.
RESET clears all the input statements.
ED call editor.
!! Command executes the operating system commands.
QUIT or EXIT () exits isql.
CTRL+C does not exit ISQL and ends queries.
Only if the command terminator GO (default), RESET, ED,..., EXIT, QUIT and CTRL+C appear at the beginning of a line (followed by the ISQL prompt) can they be identified. ISQL ignores any input after these keywords in the same row.
GO indicates the end of a batch and the execution of any cached Transact-SQL statement. At the end of each input line, press the ENTER key, and ISQL will cache the statement of the row.
When you type GO and press the ENTER key, all current cached statements will be sent to SQL Server 2000 as a batch.
The current ISQL utility works as if there were implicit GO at the end of any executed script, so all the statements in the script would be executed. Some of the earlier versions of ISQL will not send any statements to the server unless there is at least one GO in the output script. None of the statements after the last GO is executed.
End the command by entering the line with the command terminator as the start line. You can enter an integer after the command terminator to specify the number of times the command is run. For example, if you want to execute this command 100 times, type in:
SELECT x = 1GO 100
The results are printed at the end of execution. When using ISQL, the number of characters per line is limited to 1000. Long statements should be written across multiple lines.
Through at the beginning of the type ED, the user can call in the buffer on the current query editor. The editor is defined in the EDITOR environment variable. The default editor is "Edit" for MS-DOS and Windows NT". You can specify other editors by setting EDITOR environment variables. For example, if you want to specify the default editor as Notepad, enter at the operating system prompt:
SET EDITOR=notepad
For more information about where to find or run this utility, see the command prompt utilities.
The operating system command starts a row with two exclamation marks, and then enters the command, and also executes the operating system commands. The DOSKEY command revocation tool can be used to retract and modify the ISQL statements previously entered on the computer running Windows NT. Typing RESET clears existing query buffers.
When running stored procedures,
ISQL prints a blank line between each result set in batch processing. In addition, if the statement has not been applied, the "0 rows affected" message will not appear.
Interactive use of ISQL. If you want to interact with ISQL, type the ISQL command (and any options) at the command prompt.
You can read from a file that contains a query executed by ISQL (such as Stores.qry) by typing a descending command:
ISQL /U Alma /P /i stores.qry
The file must contain a command terminator.
You can read in a file that contains a query, such as Titles.qry, by typing a descending command and directing the result to another file:
ISQL /U Alma /P /i titles.qry /o titles.res
Interactive use of ISQL, in order to read the operating system files into the command buffer, you can use: R file_name. Do not include the command terminator in the file; after the completion of the edit, the interactive input terminator.
Insert annotations can be included in the Transact-SQL statement submitted to SQL Server 2000 by isql. Allow the use of two kinds of types: comment style -- and / * * /...
For more information, see the use of annotations.
Using EXIT to return ISQL results, you can use the result of the SELECT statement as the return value of isql. The first column of the first result line is converted to an integer of 4 bytes (long integer). MS-DOS transfers low byte to parent process or operating system error level. Windows NT passes the entire 4 byte integer. Grammar as:
EXIT (query)
For example:
EXIT (SELECT @@rowcount)
EXIT (SELECT 5)
You can also include the EXIT parameter as part of the batch file. For example:
ISQL /Q "EXIT" (SELECT COUNT (*) FROM'%1')"
The ISQL utility passes all the input from parentheses () to the server. EXIT () statements can span lines. If the stored system procedure selects a collection and returns a value, only the selected content is returned. The EXIT () statement with no arguments between parentheses performs all the contents before this statement in the batch, and then exits without returning the value.
There are four kinds of EXIT format:
EXIT does not perform batch processing and exits immediately without return value.
EXIT () quit after executing batch, no return value.
EXIT (query) executes batch processing including queries, returns the result of the query, and then exits.
RAISERROR with a state of 127. If RAISERROR is used in the ISQL script and the state 127 appears, ISQL will exit and return the message ID to the client program. For example:
RAISERROR (50001, 10, 127)
The error will cause the ISQL script to terminate and return the message ID 50001 to the client program.
SQL Server retains the return value from -1 to -99; ISQL defines the following values:
-100 encountered errors before selecting a return value. -101 cannot find rows when the return value is selected. Conversion error when -102 returns a value.。