osql /U alma /P /i
(Includes update and delete statements, however, GO is not required)
----------------------------------------------------
osql utility
The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility communicates with the server via ODBC.
grammar
osql
[-?] ¦
[-L] ¦
[
{
{-U login_id [-P password]}
¦ -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-?
Shows a syntax summary of the osql switch.
-L
Lists the names of the servers configured locally and the servers broadcast on the network.
-U login_id
User login ID. Login ID is case sensitive.
-P password
It is the password specified by the user. If the -P option is not used, osql will prompt 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 a default password for the current session. Therefore, there is no need to set a password in the batch file by hard coding.
If no password is specified for the -P option, osql first checks the OSQLPASSWORD variable. If the value is not set, osql uses the default password (NULL). The following example sets the OSQLPASSWORD variable at a command prompt and accesses the osql utility:
C:>SET OSQLPASSWORD=abracadabra
C:>osql
-E
Use a trust connection without requesting a password.
-S server_name[instance_name]
Specify the Microsoft® SQL Server™ 2000 instance to connect to. Specify server_name on this 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 executing osql from a remote computer on the network.
-H wksta_name
It is the name of the workstation. The workstation name is stored in and displayed by sp_who. If this option is not specified, the current computer name is taken.
-d db_name
A USE db_name statement is issued when starting osql.
-l time_out
Specifies the number of seconds before the osql login timeout. The default timeout for logging in to osql is 8 seconds.
-t time_out
Specifies the number of seconds before the command timeout. If the time_out value is not specified, the command will not time out.
-h headers
Specifies the number of rows to print between column headers. By default, the title is printed once for each query result set. Use –1 to specify that the title is not printed. If -1 is used, there must be no space between the parameter and the setting (can be -h-1, cannot be -h –1).
-s col_separator
Specifies the column separator character, which defaults to spaces. To use characters that have special meanings for the operating system (for example, ¦ ; & < >), 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
Allows you to request packets of different sizes. The valid values of packet_size are between 512 and 65535. The default value of osql is the server's default value. An increase in packet size can improve the performance of larger script executions, in which the number of SQL statements between GO commands is important. Microsoft's tests show that the 8192 is the fastest setup typical for large-capacity replication operations. A larger packet size can be requested, but if the request cannot be approved, osql defaults to the server's default.
-e
Echo input.
-I
Set the QUOTED_IDENTIFIER connection option to enable.
-D data_source_name
Connect to an ODBC data source defined with the ODBC driver for Microsoft SQL Server. osql connection uses the options specified in this data source.
Description 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 alone in a line. When resetting command terminators, do not use Transact-SQL reserved words or characters that have special meanings to the operating system, regardless of whether they are fronted by backslashes.
-q "query"
The query is executed when starting osql, but it does not exit osql when the query is completed. (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 quotes and anything embedded in the query in single quotes.
-Q "query"
Execute the query and exit osql immediately. Enclose the query in double quotes and anything embedded in the query in single quotes.
-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 levels for the specified or higher severity error. No information is displayed for errors with a severity level below the specified level. Use -1 to specify that all titles are returned with the message, even messages of the information class. If –1 is used, there cannot be spaces between the parameter and the setting (can be -m-1, cannot be -m -1).
-r {0 ¦ 1}
Redirect message output to screen (stderr). If no parameter is specified, or if the parameter is specified, only error messages with a severity level of 17 or higher are redirected. If the parameter specified is 1, all message outputs (including "print") will be redirected.
-i input_file
Identifies a file that contains a batch of SQL statements or stored procedures. The less than ( <) comparison operator can be used instead of –i.
-o output_file
Identifies the file that received output from osql. The greater 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. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level of 10 or higher; otherwise, it returns 0. Microsoft MS-DOS® batch files 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 into character data.
-O
To match the behavior of earlier versions of isql, specify that some osql features are disabled. The following functions are disabled:
EOF Batch Processing
Automatic console width adjustment
Wide information
Also set the default value of DOS ERRORLEVEL to –1.
Comments
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 use QUIT or EXIT to exit osql.
If the username is not specified when starting osql, SQL Server 2000 checks environment variables and uses them, such as osqluser=(user) or osqlserver=(server). If the environment variable is not set, the workstation username is used. If no server is specified, the workstation name is used.
If neither the -U or -P options are used, SQL Server 2000 will attempt to connect using Windows Authentication Mode. Authentication is based on a Microsoft Windows NT® user account running osql.
The osql utility uses the ODBC API. This utility uses the SQL Server ODBC driver default settings for SQL Server 2000 SQL-92 connection options. For more information, see Effects of SQL-92 Options.
For information about where and how this utility works, see Getting Started with Command Prompt Utility.
OSQL Commands
In addition to the Transact-SQL statements used in osql, the commands in the following table are also available.
Command Description
GO All statements entered after executing the last GO command.
RESET Clears all statements entered.
ED calls the editor.
!! command Executes operating system commands.
QUIT or EXIT( ) exit osql.
CTRL+C key End the query without exiting osql.
It is only recognized if the command terminator GO (default), RESET, ED, !!, EXIT, QUIT, and CTRL+C appear at the beginning of a line (following the osql prompt). osql ignores anything entered after these keywords in the same line.
GO indicates the end of a batch and the execution of any cached Transact-SQL statements. When ENTER is pressed at the end of each input line, osql caches the statements for this line. When you type GO and press ENTER, all currently cached statements are sent to SQL Server as batches.
The current osql utility works as if there is an implicit GO at the end of any executed script, so all statements in the script will be executed. No statement after the last GO will be executed.
End the command by typing the 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 execution. osql does not accept results with more than 1,000 characters per line. Long sentences should be written across multiple lines.
By typing ED at the beginning of the line, the user can call the editor on the current query buffer. The editor is defined in the EDITOR environment variable. The default editor is "edit". Other editors can be specified by setting the EDITOR environment variable. For example, to specify the default editor as Notepad, enter:
SET EDITOR=notepad
Operating system commands
Operating system commands can also be executed by starting a line with two exclamation marks (!!) and then entering commands. The command withdrawal function of Windows NT can be used to withdraw and modify osql statements. Type RESET to clear an existing query buffer.
When running stored procedures, osql prints a blank line between each result set in the batch. Additionally, if no statement applied to the executed statement, the "Line 0 affected" message will 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 (for example) containing a query executed by osql by typing a command similar to the downline:
osql /U alma /P /i
The file must contain a command terminator.
You can read in a file containing the query (for example) by typing a command like downline and direct the result to another file:
osql /U alma /P /i /o
When using osql interactively, in order to read the operating system file into the command buffer, you can use: r file_name. Do not include command terminators in the file; enter the terminator interactively after editing is completed.
Insert comments
Comments can be included in the Transact-SQL statement submitted to SQL Server by osql. Two comment style types are allowed: -- and /*...*/.
For more information, see Using Comments.
Use EXIT to return osql results
The result of the SELECT statement can be used as the return value of osql. The first column of the first result row is converted into 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 EXIT parameters 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 collection and returns a value, only the selected content is returned. The EXIT( ) statement with no parameters between parentheses executes everything before this statement in the batch, and exits without returning the value.
There are four EXIT formats:
EXIT
Exit immediately without executing batch processing, no return value.
EXIT( )
Exit after batch processing is executed, no return value.
EXIT(query)
Execute batch processing including query, return the result of the query and exit.
RAISERROR with status 127.
If RAISERROR is used in an 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 the message ID 50001 to the client program.
SQL Server retains return values between -1 and -99; osql defines the following values:
-100
An error encountered before selecting a return value.
-101
The row was not found when selecting the return value.
-102
A conversion error occurred while selecting the return value.
Show money and smallmoney data types
osql only uses two decimal places to display the money and smallmoney data types, but SQL Server uses four decimal places to store the values of these two types of data internally. Please see 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 it is in full decimal places.
'----------------------------------------------------------------------------------------------------------
First save your statement in a .sql file.
Then write this in the .bat file
osql -s [server] -U [username] -P [password] -d [database] -n -i [.sql file]
Remove the brackets
(Includes update and delete statements, however, GO is not required)
----------------------------------------------------
osql utility
The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility communicates with the server via ODBC.
grammar
osql
[-?] ¦
[-L] ¦
[
{
{-U login_id [-P password]}
¦ -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-?
Shows a syntax summary of the osql switch.
-L
Lists the names of the servers configured locally and the servers broadcast on the network.
-U login_id
User login ID. Login ID is case sensitive.
-P password
It is the password specified by the user. If the -P option is not used, osql will prompt 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 a default password for the current session. Therefore, there is no need to set a password in the batch file by hard coding.
If no password is specified for the -P option, osql first checks the OSQLPASSWORD variable. If the value is not set, osql uses the default password (NULL). The following example sets the OSQLPASSWORD variable at a command prompt and accesses the osql utility:
C:>SET OSQLPASSWORD=abracadabra
C:>osql
-E
Use a trust connection without requesting a password.
-S server_name[instance_name]
Specify the Microsoft® SQL Server™ 2000 instance to connect to. Specify server_name on this 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 executing osql from a remote computer on the network.
-H wksta_name
It is the name of the workstation. The workstation name is stored in and displayed by sp_who. If this option is not specified, the current computer name is taken.
-d db_name
A USE db_name statement is issued when starting osql.
-l time_out
Specifies the number of seconds before the osql login timeout. The default timeout for logging in to osql is 8 seconds.
-t time_out
Specifies the number of seconds before the command timeout. If the time_out value is not specified, the command will not time out.
-h headers
Specifies the number of rows to print between column headers. By default, the title is printed once for each query result set. Use –1 to specify that the title is not printed. If -1 is used, there must be no space between the parameter and the setting (can be -h-1, cannot be -h –1).
-s col_separator
Specifies the column separator character, which defaults to spaces. To use characters that have special meanings for the operating system (for example, ¦ ; & < >), 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
Allows you to request packets of different sizes. The valid values of packet_size are between 512 and 65535. The default value of osql is the server's default value. An increase in packet size can improve the performance of larger script executions, in which the number of SQL statements between GO commands is important. Microsoft's tests show that the 8192 is the fastest setup typical for large-capacity replication operations. A larger packet size can be requested, but if the request cannot be approved, osql defaults to the server's default.
-e
Echo input.
-I
Set the QUOTED_IDENTIFIER connection option to enable.
-D data_source_name
Connect to an ODBC data source defined with the ODBC driver for Microsoft SQL Server. osql connection uses the options specified in this data source.
Description 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 alone in a line. When resetting command terminators, do not use Transact-SQL reserved words or characters that have special meanings to the operating system, regardless of whether they are fronted by backslashes.
-q "query"
The query is executed when starting osql, but it does not exit osql when the query is completed. (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 quotes and anything embedded in the query in single quotes.
-Q "query"
Execute the query and exit osql immediately. Enclose the query in double quotes and anything embedded in the query in single quotes.
-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 levels for the specified or higher severity error. No information is displayed for errors with a severity level below the specified level. Use -1 to specify that all titles are returned with the message, even messages of the information class. If –1 is used, there cannot be spaces between the parameter and the setting (can be -m-1, cannot be -m -1).
-r {0 ¦ 1}
Redirect message output to screen (stderr). If no parameter is specified, or if the parameter is specified, only error messages with a severity level of 17 or higher are redirected. If the parameter specified is 1, all message outputs (including "print") will be redirected.
-i input_file
Identifies a file that contains a batch of SQL statements or stored procedures. The less than ( <) comparison operator can be used instead of –i.
-o output_file
Identifies the file that received output from osql. The greater 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. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level of 10 or higher; otherwise, it returns 0. Microsoft MS-DOS® batch files 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 into character data.
-O
To match the behavior of earlier versions of isql, specify that some osql features are disabled. The following functions are disabled:
EOF Batch Processing
Automatic console width adjustment
Wide information
Also set the default value of DOS ERRORLEVEL to –1.
Comments
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 use QUIT or EXIT to exit osql.
If the username is not specified when starting osql, SQL Server 2000 checks environment variables and uses them, such as osqluser=(user) or osqlserver=(server). If the environment variable is not set, the workstation username is used. If no server is specified, the workstation name is used.
If neither the -U or -P options are used, SQL Server 2000 will attempt to connect using Windows Authentication Mode. Authentication is based on a Microsoft Windows NT® user account running osql.
The osql utility uses the ODBC API. This utility uses the SQL Server ODBC driver default settings for SQL Server 2000 SQL-92 connection options. For more information, see Effects of SQL-92 Options.
For information about where and how this utility works, see Getting Started with Command Prompt Utility.
OSQL Commands
In addition to the Transact-SQL statements used in osql, the commands in the following table are also available.
Command Description
GO All statements entered after executing the last GO command.
RESET Clears all statements entered.
ED calls the editor.
!! command Executes operating system commands.
QUIT or EXIT( ) exit osql.
CTRL+C key End the query without exiting osql.
It is only recognized if the command terminator GO (default), RESET, ED, !!, EXIT, QUIT, and CTRL+C appear at the beginning of a line (following the osql prompt). osql ignores anything entered after these keywords in the same line.
GO indicates the end of a batch and the execution of any cached Transact-SQL statements. When ENTER is pressed at the end of each input line, osql caches the statements for this line. When you type GO and press ENTER, all currently cached statements are sent to SQL Server as batches.
The current osql utility works as if there is an implicit GO at the end of any executed script, so all statements in the script will be executed. No statement after the last GO will be executed.
End the command by typing the 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 execution. osql does not accept results with more than 1,000 characters per line. Long sentences should be written across multiple lines.
By typing ED at the beginning of the line, the user can call the editor on the current query buffer. The editor is defined in the EDITOR environment variable. The default editor is "edit". Other editors can be specified by setting the EDITOR environment variable. For example, to specify the default editor as Notepad, enter:
SET EDITOR=notepad
Operating system commands
Operating system commands can also be executed by starting a line with two exclamation marks (!!) and then entering commands. The command withdrawal function of Windows NT can be used to withdraw and modify osql statements. Type RESET to clear an existing query buffer.
When running stored procedures, osql prints a blank line between each result set in the batch. Additionally, if no statement applied to the executed statement, the "Line 0 affected" message will 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 (for example) containing a query executed by osql by typing a command similar to the downline:
osql /U alma /P /i
The file must contain a command terminator.
You can read in a file containing the query (for example) by typing a command like downline and direct the result to another file:
osql /U alma /P /i /o
When using osql interactively, in order to read the operating system file into the command buffer, you can use: r file_name. Do not include command terminators in the file; enter the terminator interactively after editing is completed.
Insert comments
Comments can be included in the Transact-SQL statement submitted to SQL Server by osql. Two comment style types are allowed: -- and /*...*/.
For more information, see Using Comments.
Use EXIT to return osql results
The result of the SELECT statement can be used as the return value of osql. The first column of the first result row is converted into 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 EXIT parameters 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 collection and returns a value, only the selected content is returned. The EXIT( ) statement with no parameters between parentheses executes everything before this statement in the batch, and exits without returning the value.
There are four EXIT formats:
EXIT
Exit immediately without executing batch processing, no return value.
EXIT( )
Exit after batch processing is executed, no return value.
EXIT(query)
Execute batch processing including query, return the result of the query and exit.
RAISERROR with status 127.
If RAISERROR is used in an 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 the message ID 50001 to the client program.
SQL Server retains return values between -1 and -99; osql defines the following values:
-100
An error encountered before selecting a return value.
-101
The row was not found when selecting the return value.
-102
A conversion error occurred while selecting the return value.
Show money and smallmoney data types
osql only uses two decimal places to display the money and smallmoney data types, but SQL Server uses four decimal places to store the values of these two types of data internally. Please see 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 it is in full decimal places.
'----------------------------------------------------------------------------------------------------------
First save your statement in a .sql file.
Then write this in the .bat file
osql -s [server] -U [username] -P [password] -d [database] -n -i [.sql file]
Remove the brackets