Call the SQL script file operation table generated by MS SQL Server 2000:
Master.dbo.xp_cmdshell 'osql -d "mydb" -u "sa" -p "sapassword" -i "x: /xxx.sql"
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 column_width] [-w color_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
-?
Display the syntax summary of the OSQL switch.
-L
List the names of the server configured locally and the name of the server broadcasting on the network.
-U login_id
User login ID. Login ID is case sensitive.
-P Password
Is the password specified by the user. If the -p option is not used, OSQL will prompt the password. If you use the -p option at the end of the command prompt without password, OSQL uses the default password (NULL). The password is case sensitive.
The OSQLPassword environment variable allows you to set the default password for the current session. Therefore, it is not necessary to set a password in the batch file by hard coding.
If you do not specify a password 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, then access the OSQL utility:
C: /> set osqlpassword = Abracadabra
C: /> OSQL
-E
Use trust connections without requesting a password.
-S server_name [/ instance_name]
Specifies the Microsoft® SQL ServerTM 2000 instance to connect. Specify server_name on this server to connect to the default instance of SQL Server. Specify server_name / instance_name on this server to connect to an instance of a named SQL Server 2000. If the server is not specified, OSQL will connect to the SQL Server default instance on the local computer. This option is required when performing OSQL from a remote computer on the network.
-H wksta_name
It 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
A USE DB_NAME statement is issued when OSQL is started.
-l Time_out
Specifies the number of seconds before the OSQL login timeout. The default timeout logged in to OSQL is 8 seconds.
-t Time_out
Specifies the number of seconds before the command is timeout. If the TIME_OUT value is not specified, the command will not be timeout.
-H Headers
Specifies the number of rows to print between column headers. The default is to print a title for each query result set. Use -1 specified that you do not print the title. If -1 is used, there must be a space between parameters and settings (can be -h-1, no -h -1).
-S col_separator
Specifies the column separator character, which defaults to spaces. To use characters (for example, |; & <>) to the operating system, use the boutique (").
-w colorn_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 rows.
-a packet_size
Enables you to request different sizes of packets. The valid value of packet_size is between 512 and 65535. OSQL's default is the default value of the server. The increase in packet size can improve the performance of larger scripts, and the number of SQL statements between the GO commands is important in this execution. Microsoft's test indicates that 8192 is the fastest set of large-capacity replication operations. A larger packet size can be requested, but if the request cannot be approved, OSQL defaults to the default value of the server.
-e
Back to the input.
-I
Set the quoted_identifier connection option to open.
-D Data_Source_name
Connect to the ODBC data source defined by Microsoft SQL Server's ODBC driver. OSQL connection uses the options specified in the data source.
Note This option does not apply to the data source defined by other drivers.
-C cmd_end
Specify the command terminator. By default, the command is terminated by entering Go alone and sent to SQL Server 2000. When resetting the command terminator, do not use the TRANSACT-SQL reserved word or character to the operating system, regardless of whether there is a backslash in front.
-q "query"
When you start OSQL, execute the query, but do not quit OSQL when the query is complete. (Note Query statements should not contain GO). If you have a query from a batch file, use% Variables or Environment% Variables%. E.g:
Set Table = SYSOBJECTS
OSQL / Q "SELECT * FROM% TABLE%"
Causes the query with double quotes to cause any content embedded in the query.
-Q "query"
Execute the query and exit OSQL immediately. Causes the query with double quotes to cause any content embedded in the query.
NN
Remove the number and prompt symbol from the input line (>).
-m Error_level
Custom error message display. Display the number of messages, status, and error levels of the specified or higher severity error. Any information that does not show the wrong level below the specified level. Use -1 Specify that all headings are returned with the message, even if the message of the information class. If -1 is used, there is no space between the parameters and settings (can be -m-1, can not be -m -1).
-r {0 | 1}
Redirect the message output to the screen (stderr). If the parameters are not specified, or the specified parameter is 0, only an error message of the severity of 17 or higher is redirected. If the specified parameter is 1, all messages are redirected (including "Print").
-i input_file
Identify files that contain a batch of SQL statements or stored procedures. Small than (<) The comparison operator can be used instead of -i.
-o output_file
Identify files that receive output from OSQL. More than (>) comparison operators can be used instead-O.
If INPUT_FILE is not Unicode and does not specify -U, Output_File will be stored as an OEM format. If INPUT_FILE is Unicode or specified -U, Output_File will be stored as a Unicode format. -p
Print performance statistics.
-b
Specifies whether the 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 of returning to the DOS Errorlevel variable is 1; otherwise it returns 0. The Microsoft MS-DOS® batch file can test the value of DOS Errorlevel and handle errors appropriately.
-u
Specifies Output_File stored as a 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 earlier version of ISQL, specify some OSQL features. The following functions are deactivated:
EOF batch console width automatically adjusts wide information At the same time, the default value of DOS ERRORLEVEL is also set to -1.
-?
Display the syntax summary of the OSQL switch.
-L
List the names of the server configured locally and the name of the server broadcasting on the network.
-U login_id
User login ID. Login ID is case sensitive.
-P Password
Is the password specified by the user. If the -p option is not used, OSQL will prompt the password. If you use the -p option at the end of the command prompt without password, OSQL uses the default password (NULL). The password is case sensitive.
The OSQLPassword environment variable allows you to set the default password for the current session. Therefore, it is not necessary to set a password in the batch file by hard coding.
If you do not specify a password 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, then access the OSQL utility:
C: /> set osqlpassword = Abracadabra
C: /> OSQL
-E
Use trust connections without requesting a password.
-S server_name [/ instance_name]
Specifies the Microsoft® SQL ServerTM 2000 instance to connect. Specify server_name on this server to connect to the default instance of SQL Server. Specify server_name / instance_name on this server to connect to an instance of a named SQL Server 2000. If the server is not specified, OSQL will connect to the SQL Server default instance on the local computer. This option is required when performing OSQL from a remote computer on the network.
-H wksta_name
It 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
A USE DB_NAME statement is issued when OSQL is started.
-l Time_out
Specifies the number of seconds before the OSQL login timeout. The default timeout logged in to OSQL is 8 seconds.
-t Time_out
Specifies the number of seconds before the command is timeout. If the TIME_OUT value is not specified, the command will not be timeout.
-H Headers
Specifies the number of rows to print between column headers. The default is to print a title for each query result set. Use -1 specified that you do not print the title. If -1 is used, there must be a space between parameters and settings (can be -h-1, no -h -1).
-S col_separator
Specifies the column separator character, which defaults to spaces. To use characters (for example, |; & <>) for the operating system, use the bid to 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 rows.
-a packet_size
Enables you to request different sizes of packets. The valid value of packet_size is between 512 and 65535. OSQL's default is the default value of the server. The increase in packet size can improve the performance of larger scripts, and the number of SQL statements between the GO commands is important in this execution. Microsoft's test indicates that 8192 is the fastest set of large-capacity replication operations. A larger packet size can be requested, but if the request cannot be approved, OSQL defaults to the default value of the server.
-e
Back to the input.
-I
Set the quoted_identifier connection option to open.
-D Data_Source_name
Connect to the ODBC data source defined by Microsoft SQL Server's ODBC driver. OSQL connection uses the options specified in the data source.
Note This option does not apply to the data source defined by other drivers.
-C cmd_end
Specify the command terminator. By default, the command is terminated by entering Go alone and sent to SQL Server 2000. When resetting the command terminator, do not use the TRANSACT-SQL reserved word or character to the operating system, regardless of whether there is a backslash in front.
-q "query"
When you start OSQL, execute the query, but do not quit OSQL when the query is complete. (Note Query statements should not contain GO). If you have a query from a batch file, use% Variables or Environment% Variables%. E.g:
Set Table = SYSOBJECTS
OSQL / Q "SELECT * FROM% TABLE%"
Causes the query with double quotes to cause any content embedded in the query.
-Q "query"
Execute the query and exit OSQL immediately. Causes the query with double quotes to cause any content embedded in the query.
NN
Remove the number and prompt symbol from the input line (>).
-m Error_level
Custom error message display. Display the number of messages, status, and error levels of the specified or higher severity error. Any information that does not show the wrong level below the specified level. Use -1 Specify that all headings are returned with the message, even if the message of the information class. If -1 is used, there is no space between the parameters and settings (can be -m-1, can not be -m -1).
-r {0 | 1}
Redirect the message output to the screen (stderr). If the parameters are not specified, or the specified parameter is 0, only an error message of the severity of 17 or higher is redirected. If the specified parameter is 1, all messages are redirected (including "Print").
-i input_file
Identify files that contain a batch of SQL statements or stored procedures. Small than (<) The comparison operator can be used instead of -i.
-o output_file
Identify files that receive output from OSQL. More than (>) comparison operators can be used instead-O.
If INPUT_FILE is not Unicode and does not specify -U, Output_File will be stored as an OEM format. If INPUT_FILE is Unicode or specified -U, Output_File will be stored as a Unicode format.
-p
Print performance statistics.
-b
Specifies whether the 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 of returning to the DOS Errorlevel variable is 1; otherwise it returns 0. The Microsoft MS-DOS® batch file can test the value of DOS Errorlevel and handle errors appropriately. -u
Specifies Output_File stored as a 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 earlier version of ISQL, specify some OSQL features. The following functions are deactivated:
EOF batch console width automatically adjusts wide information At the same time, the default value of DOS ERRORLEVEL is also set to -1.
