Shulou(Shulou.com)06/03 Report--

This article will explain in detail how to call SQL through batch processing. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Osql / U alma / P / i stores.bat

(update, delete statements are included in stores.bat, however, GO is not required)

Osql utility

The osql utility enables you to enter Transact-SQL statements, system procedures, and script files. The utility communicates with the server through ODBC.

Grammar

Osql

[-?]

[- L]

[

{

{- U login_id [- P password]}

Ye-E

}

[- S server_ name [instance _ name]] [- H wksta_name] [- d db_name]

[- l time_out] [- t time_out] [- h headers]

[- s col_separator] [- w column_width] [- a packet_size]

[- e] [- I] [- D data_source_name]

[- c cmd_end] [- Q "query"] [- Q "query"]

[- n] [- m error_level] [- r {0'1}]

[- I input_file] [- o output_file] [- p]

[- b] [- u] [- R] [- O]

]

Parameter -?

Displays a syntax summary of the osql switch.

-L

Lists the names of servers configured locally and servers broadcast on the network.

-U login_id

The user logs in to ID. Login ID is case sensitive.

-P password

Is the password specified by the user. If the-P option is not used, osql prompts for a password. If you use the-P option at the end of the command prompt without a password, osql uses the default password (NULL). Passwords are case sensitive.

The OSQLPASSWORD environment variable allows you to set the default password for the current session. Therefore, you do not need to hard-code to set the password in the batch file.

If no password is specified for the-P option, osql first checks the OSQLPASSWORD variable. If no value is set, osql uses the default password (NULL). The following example sets the OSQLPASSWORD variable at the command prompt and then accesses the osql utility:

C: > SET OSQLPASSWORD=abracadabra

C: > osql

-E

Use a trusted connection without requesting a password.

-S server_ name [instance _ name]

Specifies the instance of Microsoft ®SQL Server ™2000 to connect to. Specify server_name on the server to connect to the default instance of SQL Server. Specify server_nameinstance_name on this server to connect to an instance of named SQL Server 2000. If no server is specified, osql connects to the default instance of SQL Server on the local computer. This option is required when performing osql from a remote computer on the network.

-H wksta_name

Is the name of the workstation. The workstation name is stored in sysprocesses.hostname and displayed by sp_who. If this option is not specified, the current computer name is used.

-d db_name

Issue a USE db_name statement when you start osql.

-l time_out

Specifies the number of seconds before the osql login times out. The default timeout for logging in to osql is 8 seconds.

-t time_out

Specifies the number of seconds before the command times out. If no time_out value is specified, the command does not time out.

-h headers

Specifies the number of rows to print between column headings. The default is to print the title once for each query result set. Use-1 to specify that the title is not printed. If you use-1, there must be no space between the parameter and the setting (it can be-hmur1, not-hmur1).

-s col_separator

Specifies the column delimiter character, which defaults to a space. To use characters that have a special meaning to the operating system (for example, characters; &

), enclose the character in double quotes (")

-w column_width

Allows the user to set the width of the screen output. The default is 80 characters. When the output line reaches its maximum screen width, it is split into multiple lines.

-a packet_size

Enables you to request packets of different sizes. Valid values for packet_size are between 512 and 65535. The default value for osql is the default value for the server. An increase in packet size can improve the performance of larger script execution, where the number of SQL statements between GO commands is important. Microsoft tests show that 8192 is the typical fastest setting for bulk copy operations. You can request a larger packet size, but if the request cannot be approved, osql defaults to the default value for the server.

-e

Echo input.

-I

Set the QUOTED_IDENTIFIER connection option to on.

-D data_source_name

Connect to an ODBC data source defined with Microsoft SQL Server's ODBC driver. The osql connection uses the options specified in the data source.

Indicates that this option does not apply to data sources defined by other drivers.

-c cmd_end

Specifies the command Terminator. By default, the command is terminated and sent to SQL Server 2000 by entering GO on a separate line. When resetting command Terminators, do not use Transact-SQL reserved words or characters that have a special meaning to the operating system, regardless of whether they are preceded by a backslash or not.

-Q "query"

Executes the query when osql is started, but does not exit osql when the query is complete. (note that the query statement should not contain GO.) If you issue a query from a batch file, use variables or environment variables%. For example:

SET table = sysobjects

Osql / Q "Select * from table%"

Enclose the query in double quotation marks and anything embedded in the query in single quotation marks.

-Q "query"

Execute the query and exit osql immediately. Enclose the query in double quotation marks and anything embedded in the query in single quotation marks.

-n

Remove the number and prompt symbol (>) from the input line.

-m error_level

Customize the display of error messages. Displays the number of messages, status, and error level for the specified or higher severity error. Does not display any information about errors with a severity level lower than the specified level. Use-1 to specify that all headers are returned with the message, even messages of the information class. If you use-1, there can be no space between the parameter and the setting (it can be-mmur1, not-mmer1).

-r {0 # 1}

Redirect message output to the screen (stderr). If no parameter is specified, or if the parameter is 0, only error messages with a severity level of 17 or higher are redirected. If the specified parameter is 1, all message output (including "print") will be redirected.

-I input_file

Identifies the file that contains a batch of SQL statements or stored procedures. The less than () comparison operator can be used instead of-o.

If input_file is not Unicode and-u is not specified, output_file is stored in OEM format. If input_file is Unicode or-u is specified, output_file is stored in Unicode format.

-p

Print performance statistics.

-b

Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. When the severity level of the SQL Server error message is 10 or higher, the value returned to the DOS ERRORLEVEL variable is 1; otherwise, 0 is returned. The Microsoft MS-DOS ®batch file can test the value of DOS ERRORLEVEL and handle errors appropriately.

-u

Specifies that output_file is stored in Unicode format, regardless of the format of input_file.

-R

Specifies that the SQL Server ODBC driver uses client settings when converting currency, date, and time data to character data.

-O

To match the behavior of previous versions of isql, specify that some osql features be disabled. The following features are disabled:

EOF batch processing

Automatic adjustment of console width

Wide information

The default value for DOS ERRORLEVEL is also set to-1.

Annotation

The osql utility starts directly from the operating system and uses the case-sensitive options listed in this article. After startup, osql accepts SQL statements and sends them interactively to SQL Server. The results are formatted and displayed on the screen (stdout). You can exit osql using QUIT or EXIT.

If you do not specify a user name when you start osql, SQL Server 2000 checks for environment variables and uses them, such as osqluser= (user) or osqlserver= (server). If the environment variable is not set, the workstation user name is used. If no server is specified, the workstation name is used.

If neither the-U nor-P options are used, SQL Server 2000 attempts to connect using Windows authentication mode. Authentication is based on the Microsoft Windows NT ®user account running osql.

The osql utility uses ODBC API. This utility uses the SQL Server ODBC driver default settings for the SQL Server 2000 SQL-92 connection option. For more information, see the effect of the SQL-92 option.

For information about the location and operation of this utility, see getting started with the Command prompt Utility.

OSQL command

In addition to the Transact-SQL statements used in osql, the commands in the following table are also available.

Command description

All statements entered by GO after the last GO command was executed.

RESET clears all statements that have been entered.

ED calls the editor.

!! Command executes operating system commands.

QUIT or EXIT () exits osql.

The CTRL+C key ends the query without exiting osql.

The command Terminator GO (default), RESET, ED,!, EXIT, QUIT, and CTRL+C are recognized only when they appear at the beginning of a line (immediately following the osql prompt). Osql ignores anything entered after these keywords on the same line.

GO indicates the end of a batch and the execution of any Transact-SQL statements that have been cached. When you press ENTER at the end of each input line, osql caches the statement for that line. When you press the ENTER key after typing GO, all currently cached statements are sent to SQL Server as a batch.

The current osql utility works as if there is an implicit GO at the end of any script being executed, so all statements in the script will be executed. No statements after the last GO are executed.

End the command by typing a line that starts with the command Terminator. You can enter an integer after the command Terminator to specify the number of times the command is run. For example, to execute this command 100 times, type:

SELECT x = 1

GO 100

Print the result once at the end of the execution. Osql does not accept results with more than 1000 characters per line. Long sentences should be written across multiple lines.

By typing ED at the beginning of the line, the user can invoke the editor on the current query buffer. The editor is defined in the EDITOR environment variable. The default editor is edit. You can specify other editors by setting the EDITOR environment variable. For example, to specify the default editor as Notepad, enter at the operating system prompt:

SET EDITOR=notepad

Operating system command

By using two exclamation points (!) You can also execute operating system commands the way you start a line and then enter a command. Windows NT's command recall function can be used to recall and modify osql statements. Type RESET to clear the existing query buffer.

When you run a stored procedure, osql prints a blank line between each result set in the batch. In addition, if it is not applied to an executed statement, the "0 row affected" message does not appear.

Interactive use of osql

To use osql interactively, type the osql command (and any options) at the command prompt.

You can read in a file that contains a query executed by osql, such as Stores.qry, by typing a command similar to the following line:

Osql / 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, and direct the results to another file by typing a command similar to the following line:

Osql / U alma / P / i titles.qry / o titles.res

When using osql interactively, to read operating system files into the command buffer, use: r file_name. Do not include the command Terminator in the file; enter the Terminator interactively after editing.

Insert comment

Comments can be included in Transact-SQL statements submitted by osql to SQL Server. Two annotation styles are allowed:-- and / *. * /.

For more information, see working with comments.

Use EXIT to return osql results

You can use the result of the SELECT statement as the return value of osql. The first column of the first result row is converted to a 4-byte integer (long integer). MS-DOS passes low bytes to the parent process or operating system error level. Windows NT passes the entire 4-byte integer. The syntax is:

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:

Osql / Q "EXIT (SELECT COUNT (*) FROM'% 1')"

The osql utility passes everything entered in parentheses () to the server as is. If the storage system procedure selects a set and returns a value, only the selection is returned. The EXIT () statement with no parameters between parentheses executes everything before this statement in the batch and then exits without returning a value.

There are four EXIT formats:

EXIT

Exit immediately without executing the batch, with no return value.

EXIT ()

Exit after batch execution, no return value.

EXIT (query)

Execute a batch that includes a query, return the results of the query and exit.

RAISERROR with a status of 127,

If RAISERROR is used in the osql script and status 127 appears, osql exits and returns the message ID to the client. For example:

RAISERROR (50001, 10,127)

This error causes the osql script to terminate and return message ID 50001 to the client.

SQL Server retains return values between-1 and-99; osql defines the following values:

-100

The error encountered before selecting the return value.

-101

The row was not found when selecting the return value.

-102

A conversion error occurred while selecting the return value.

Display money and smallmoney data types

Osql displays money and smallmoney data types with only two decimal places, but SQL Server stores values for both types of data internally with four decimal places. Take a look at the following example:

SELECT CAST (CAST (10.3496 AS money) AS decimal (6,4))

The result of this statement is 10.3496, indicating that the value is stored as is in full decimal places.

'-

Save your statement in a .sql file first.

And then the .bat file says this.

Osql-s [server]-U [user name]-P [password]-d [database]-n-I [.sql file]

Remove the square brackets

This is the end of this article on "how to call SQL through batch processing". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.