Osql utility for Operating SQL statements

To call an SQL statement directly on the command line, follow these steps ::

Osql-E/Q "exit (truncate table cnad. dbo. ss_dataconfig )"

 

Original link: http://technet.microsoft.com/zh-cn/library/ms162806.aspx

UseOsqlA utility that can be used to input Transact-SQL statements, system processes, and script files. This utility communicates with the server through ODBC.

:
This function will be deleted in future versions of SQL Server. Avoid using this function in new development, and consider modifying the applications currently using this function. This tool willSqlcmd. For more information, see sqlcmd utility.

Syntax

osql
[-?] |
[-L] |
[
{
{-Ulogin_id [-Ppassword]} | -E }
[-Sserver_name[\instance_name]] [-Hwksta_name] [-ddb_name]
[-ltime_out] [-ttime_out] [-hheaders]
[-scol_separator] [-wcolumn_width] [-apacket_size]
[-e] [-I] [-D data_source_name]
[-ccmd_end] [-q "query"] [-Q"query"]
[-n] [-merror_level] [-r {0 | 1}]
[-iinput_file] [-ooutput_file] [-p]
[-b] [-u] [-R] [-O]
]

Parameters

-?

DisplayOsqlThe syntax of the switch.

-L

Lists the names of the Servers configured locally and the servers broadcasted on the network.

In view of the characteristics of broadcast on the network,OsqlMay not receive responses from all servers in a timely manner. Therefore, the list of servers returned by each call to this option may be different.

-U Login_id

User Logon ID. The logon ID is case sensitive.

-P Password

Password specified by the user. If not-POption,OsqlEnter the password. If you use-POption without password,OsqlThe default password (NULL) will be used ).

Do not use a blank password. Use a strong password. For more information, see strong passwords.

The password is case sensitive.

You can use the OSQLPASSWORD environment variable to set the default password for the current session. Therefore, you do not need to set a password in the batch file through hard encoding.

If you do not use-PSpecifies the password,OsqlCheck the OSQLPASSWORD variable first. If no value is setOsqlThe default password (NULL) will be used ). The following example sets the OSQLPASSWORD variable in the command prompt and then accessesOsqlUtility:

Copy code

C:\>SET OSQLPASSWORD=abracadabra
C:\>osql 

Security description:
To shield the password, do not use it at the same time-UAnd-P. InsteadOsqlAnd-UOption and other switches (not specified-P) And press Enter.OsqlYou will be prompted to enter the password. This method ensures that the password is blocked.

-E

Use a trusted connection without requesting a password.

-S Server_name[ \ Instance_name]

Specify the SQL Server instance to connect. SpecifyServer_nameConnect to the default instance of SQL Server on this Server. SpecifyServer_name\Instance_nameConnect to the name instance of SQL Server on this Server. If no server is specified,OsqlConnect to the default instance of SQL Server on the local computer. Run from a remote computer on the networkOsqlThis option is required.

-H Wksta_name

Workstation name. The workstation name is stored inSysprocesses. hostnameAnd pressSp_whoDisplay. If this option is not specified, the current computer name is used.

-D Db_name

StartOsqlIssue a USEDb_nameStatement.

-L Time_out

SpecifyOsqlThe number of seconds before the logon timeout. Log onOsqlThe default timeout time is 8 seconds.

-T Time_out

Specifies the number of seconds before the command times out. If not specifiedTime_outValue, the command will not time out.

-H Headers

Specifies the number of rows to be printed between column headers. By default, the title is printed for each query result. Use-1 to specify not to print the title. If-1 is used, there must be no space between the parameter and the setting (which can beH-1, Cannot be-H-1).

-S Col_separator

Specifies the column delimiter. The default value is space. To use a character (for example, |;<>) that has a special meaning on the operating system, enclose the character in double quotation marks.

-W Column_width

Allows you to set the screen output width. The default value is 80 characters. When the output line reaches the maximum screen width, it is split into multiple rows.

- Packet_size

You can request data packets of different sizes.Packet_sizeThe valid values are between 512 and 65535.OsqlThe default value is the default value of the server. When a large script is executed, the number of SQL statements between the GO commands is large, so increasing the number of data packets can improve the performance. Microsoft tests show that the fastest setting for large-capacity replication operations is usually 8192. Larger data packets can be requested, but if the request cannot be approvedOsqlThe default value is the default value of the server.

-E

Echo input.

-I

Set the QUOTED_IDENTIFIER connection option to enable.

-D Data_source_name

Connect to an ODBC Data Source defined by the ODBC driver for SQL Server.OsqlConnect to use the options specified in the data source.

Note:
This option is not applicable to data sources defined for other drivers.

-C Performance_end

Specifies the command Terminator. By default, you can enter a separate GO command in the line to terminate the command and send it to SQL Server. If you want to reset the command Terminator, do not use the reserved characters or characters in the Transact-SQL statement that have special meanings on the operating system, whether or not there is a backslash before it.

-Q " Query "

StartOsqlBut does not exit when the query is complete.Osql. (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:

Copy code

SET table=sys.objects
osql -E -q "select name, object_id from %table%"

Enclose a query with double quotation marks and enclose any content embedded in the query with single quotation marks.

-Q " Query "

Execute the query and exit immediatelyOsql. Enclose a query with double quotation marks and enclose any content embedded in the query with single quotation marks.

-N

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

-M Error_level

Display custom error messages. Displays the number, status, and error level of a specified or higher severity error. No error information lower than the specified level is displayed. Use-1You can specify to return all titles and their messages, even information messages. If you use-1, There is no space between the parameter and the setting (can beM-1, Cannot be-M-1).

-R{ 0| 1}

Redirect the message output to the screen (Stderr). If no parameter is specified or the specified parameter is0, Only the error message with the severity level of 11 or higher is redirected. If the specified parameter is1, 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 (<) Comparison operators can replace-I.

-O Output_file

Id slaveOsqlReceives the output file. Greater (>) Comparison operators can replace-O.

IfInput_fileNot Unicode and not specified-UIs stored in OEM format.Output_file. IfInput_fileIs Unicode or specified-UIs stored in Unicode format.Output_file.

-P

Print performance statistics.

-B

When an error occurs,OsqlExit and return a dos errorlevel value. If the severity of the SQL Server error message is 11 or higher, the value returned by the DOS ERRORLEVEL variable is 1; otherwise, the value returned is 0. Microsoft MS-DOS batch files can test the value of dos errorlevel and handle errors as appropriate.

-U

Specify whetherInput_fileWhat is the format,Output_fileAll are stored in Unicode format.

-R

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

-O

SpecifyIsqlAnd disable someOsqlFunction. The following features are disabled:

• EOF Batch Processing
  
• Auto Adjust console width
  
• Wide message
  

In addition, the default value of dos errorlevel is set to-1.

Note:
OsqlNot Supported-N,-OAnd-D.

Remarks

OsqlThe utility starts directly from the operating system and uses the case-sensitive options listed in this article. After startup,OsqlSQL statements are accepted and then sent to SQL Server in interactive mode. The result is formatted and displayed on the screen (Stdout. You can use QUIT or EXIT to EXITOsql.

If you startOsqlIf no user name is specified, SQL Server checks and uses environment variables, as shown in figureOsqluser = (User)OrOsqlserver = (Server). If no environment variable is set, the user name of the workstation is used. If no server is specified, the workstation name is used.

If-UAnd-PIf none of the options are used, SQL Server will try to connect in Microsoft Windows Authentication mode. Identity Authentication Based on runOsql.

OsqlUse ODBC APIs. For SQL Server ISO connection options, this utility uses the default settings of the SQL Server ODBC driver. For more information, see "Effects of ANSI options ".

Note:
OsqlThe utility does not support CLR user-defined data types. To process these data types, you must useSqlcmdUtility. For more information, see sqlcmd utility.

OSQL commands

BesidesOsqlIn addition to the Transact-SQL statement, you can also use the following command.

Command	Description
GO	Execute all the statements entered after the previous GO command.
RESET	Clear all input statements.
QUIT or EXIT ()	ExitOsql.
Ctrl + C	Query ended but not exitedOsql.

Note:
OsqlNot Supported !! And ED commands.

Only when the command Terminator GO (default), RESET, EXIT, QUIT, and Ctrl + C appear at the beginning of a line (followedOsqlPrompt.

GO sends a signal at the end of batch processing and execution of any cached Transact-SQL statement. When you press Enter at the end of each input line,OsqlThe statement that caches this row. When you type GO and press Enter, All cached statements are sent to SQL Server as a batch.

Use the currentOsqlWhen a utility is used, it seems that there is an implicit GO at the end of the executed script, so all statements in the script will be executed.

Enter the line ending command starting 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 for 100 times, you can type:

Copy code

SELECT x = 1
GO 100

The result is printed after execution.OsqlEach row cannot contain more than 1,000 characters. Long statements should be written across multiple lines.

Windows Command recall function can be used to recall and modifyOsqlStatement. Type RESET to clear the existing Query Buffer.

When running a stored procedure,OsqlPrint a blank line between each result set in the batch processing. In addition, if the statement is not used for execution, the message "0 rows affected" is not displayed.

Use osql in interactive mode

To use it interactivelyOsql, TypeOsqlCommand (and any options ).

By typing a command similar to the following, you can readOsqlThe file to be queried (such as Stores. qry ):

Copy code

osql -E -i stores.qry

By typing a command similar to the following, you can read the file containing the query (such as Titles. qry) and direct the result to other files:

Copy code

osql -E -i titles.qry -o titles.res

Security description:
If possible, use-EOption (trusted connection ).

Use in interactive modeOsqlTo read the operating system file into the command buffer, you can use: R File_name. This willFile_nameThe SQL script in is directly sent to the server as a batch.

Note:
UseOsqlIf the batch processing separator "GO" appears in the SQL script file, SQL Server regards it as a syntax error.

Insert comments

You canOsqlThe statements submitted to SQL Server contain comments. Two types of annotation styles are allowed: -- And /*...*/.

For more information, see use annotations.

Use EXIT to return results in osql

You can use the result of the SELECT statementOsql. The first column of the First result row is converted to a 4-byte INTEGER (Long ). The MS-DOS passes low bytes to the parent process or operating system error level. In Windows, the entire 4-byte integer is passed. Syntax:

Copy code

EXIT ( < query > )

For example:

Copy code

EXIT(SELECT @@ROWCOUNT)

You can also include the EXIT parameter in the batch file. For example:

Copy code

osql -E -Q "EXIT(SELECT COUNT(*) FROM '%1')"

OsqlUtility to enclose parentheses( )All input content is transmitted to the server as is. If a set is selected and a value is returned, only the selected content is returned. EXIT Without parameters in parentheses( )The statement will execute all the content before this statement in batches, and then exit without returning the value.

There are four EXIT formats:

• EXIT
  

Note:
Do not execute batch processing, exit immediately, and do not return values.

• EXIT( )
  

Note:
Exit after batch processing. No return value is returned.

• EXIT(Query)
  

Note:
Execute the batch processing that includes the query, return the query result, and exit.

• RAISERROR In the 127 status.
  

Note:
IfOsqlIf RAISERROR is used in the script and status 127 is displayedOsqlAnd return the Message ID to the client. For example:

Copy code

RAISERROR(50001, 10, 127)

This error will causeOsqlThe script terminates and returns Message ID 50001 to the client.

The return value 1-99 is reserved for SQL Server;OsqlYou can define the following values:

• -100
  
  An error occurred before selecting the return value.
  
• -101
  
  The row is not found when the return value is selected.
  
• -102
  
  A Conversion error occurred when selecting the return value.
  

Show the data types of Money and Smallmoney

OsqlDisplay in only two decimal placesMoneyAndSmallmoneyData type, but SQL Server uses four decimal places to internally store these two types of data values. See the following example:

Copy code

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

The result of this statement is10.3496, Indicating that the value is stored as is in full scale.