17.5.SQL Server 2000 Connection Options
17.5.1.Connection Option Parameters
When configuring an OpenLink SQL Server 2000 multi-tier Agent (sql2k_[sm]v[.exe] only and not sql_[sm]v[.exe]), the following keywords and values may be entered in the Connection Options section of the entry for the agent in the Request Broker configuration file (oplrqb.ini). All are optional.
Table17.21.
| Key | Keyword | Value(s) | Description |
|---|---|---|---|
| -A | ADDRESS | Network address of the server which is running an instance of SQL Server. | Address is usually the network name of the server, but can be other names such as a pipe, or a TCP/IP port and socket address. |
| -B | FALLBACK |
Yes (default) No Applies to SQL Server 6.5 only. |
When yes, instructs the driver to attempt connection to a fallback server if connection to a primary server fails. When no, no attempt at a fallback connection is made. This option applies only to standby servers. It does not apply to a virtual server in a cluster / failover configuration. |
| -C | AUTOTRANSLATE |
Yes (default) No |
When yes, ANSI character strings sent between the client and server are translated by converting through Unicode to minimize problems in matching extended characters between the code pages on the client and the server. |
| -D | DATABASE | Database Name | The name of the database which will be the default database for the connection. Note however, that for OpenLink Agents and Single-tier Drivers, this value is normally entered into a separate control on the Configuration module. If this control contains a name, entering this option as a connection option also could lead to the attempt to establish a connection being rejected. |
| -E | QUERYLOGFILE | Full path, name and extension of the log file. | This option applies only when Option O (QUERYLOG_ON) is set to yes. This is the file to which data for long-running queries is saved. |
| -F | ATTACHDBFILENAME | Name of the primary file of an attachable database. Include the full path and escape any \ characters if using a C character string variable. Example: c:\\MyFolder\\MyDB.mdf |
This database is attached and becomes the default database for the connection. You must also specify the database name in the parameter, DATABASE. However, see above for the Database option, -D. If the database was previously attached, SQL Server will not reattach it; it will use the attached database as the default for the connection. |
| -G | QUERYLOGTIME | Numeric character string | Applies only when Option O (QUERYLOG_ON) is set to yes. This specifies the threshold (in milliseconds) for logging long-running queries. Any query that does not get a response within this time is written to the long-running query log file (which is defined by the Option, -E, QUERYLOGFILE). |
| -H | SERVER | Server Name | Name of a server which is running an instance of SQL Server on the network. By default, this is local. |
| -L | LANGUAGE | SQL Server language name. | SQL Server language name. SQL Server can store messages for multiple languages in sysmessages. If connecting to a SQL Server with multiple languages, Language specifies which set of messages are used for the connection. |
| -N | NETWORK | Name of a network library dynamic-link library. E.g. dbnmpntw | The name need not include the path and must not include the .dll file name extension. |
| -O | QUERYLOG_ON |
Yes No (default) |
Enables or disables logging long-running query data on the connection. |
| -Q | QUOTEDID |
Yes (default) No |
When yes, ,the SQL-92 rules regarding the use of quotation marks in SQL statements are applied. When no, the legacy Transact-SQL rules apply. |
| -R | REGIONAL |
Yes No (default) |
When yes, client settings are used when converting currency, date, and time data to character data. When no, ODBC standard strings are used to represent currency, date, and time data that is converted to string data. |
| -S | STATSLOG_ON |
Yes No (default) |
When set to yes, SQL Server ODBC driver performance data is captured. |
| -T | TRUSTEDCONNECTION |
Yes No (default) |
When yes, Windows Authentication Mode is used for login validation. I.e. no User ID or Password need be supplied. When no, a User ID and password must be supplied. |
| -U | USEPROCFORPREPARE |
0 (default) 1 2 Applies to SQL Server 6.5 only. |
When 0, the SQL Server ODBC driver does not create temporary stored procedures for SQLPrepare. When 1, instructs the SQL Server ODBC driver to create temporary stored procedures when statements are prepared with SQLPrepare. The temporary stored procedures are not dropped until the connection is broken. When 2, the SQL Server ODBC driver creates temporary stored procedures for SQLPrepare, but only one procedure is created per statement handle and the procedure is dropped when the statement handle becomes invalid or a new SQL statement is prepared. |
| -W | ANSINPW |
Yes (default) No |
When yes, the driver uses ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation. When no, ANSI defined behaviors are not exposed. |
17.5.2.Entry Format
ODBC Administrator
Options should be entered into the ConnectOptions control in the format:
-<OptionLetter1> value1 -<OptionLetter2> value2 etc e.g. -C no L English
See the table above for a list of available option letters.
Oplrqb.ini Settings
Connection Options are entered following the ConnectOptions keyword. Options may be entered in the same format as in the ODBC Configuration dialog, using key letters as above for example:
ConnectOptions = {-c no l English}
Surrounding braces ({ and }) may be used but are not necessary.
Alternately, the full keywords may be used. In this case, they should be entered in the format:
ConnectOptions = {Option1=value1;Option2=value2;etc}
Semicolons should be used to separate items in the list. Surrounding braces ({ and }) may be used but are not necessary. If surrounding braces are used, any text outside the braces will be discarded. A trailing semicolon is not necessary and ought not to be used, but should not cause fatal errors if it is used.
General
Items may be entered into the list in any order. They are not case-sensitive, so upper case or lower case or a mixture of both can be used (though this may depend on the SQL Server itself).
If no option key letters or keys are used, the Agent will assume that the complete ConnectOptions entry is the name of the server running the SQL Server database i.e. it will assume it to be a value for the keyword SERVER. See the notes for this keyword in the table above.
17.5.3.Disallowed Keywords
The following Keywords should not be added to the ConnectOptions entry, as they are set by the Agent Configuration Setup dialog, or are applied automatically by the Request Broker:
| APP |
| DATABASE (see below) |
| DRIVER |
| DSN |
| FILEDSN |
| PWD |
| UID (see below) |
If any of the above Keywords are used, the agent will probably fail to log on to the Database server, as duplicate Connect Options may have been supplied.
Note: It is possible to add DATABASE= and UID= entries, provided that the Database:Name and Database:Username edit controls in the Agent Configuration dialog are left blank. Note the comments for the keyword, TrustedConnection above, which affects the handling of the UID keyword.
The OpenLink SQL2000 agent does not perform any consistency or validity checking on the ConnectOptions entry, and passes the Connection options as entered to the SQL Server. If invalid keywords or values, or unreadable entries are supplied, the SQL Server may reject the login.
The exception is the case where the ConnectOptions entry contains no recognizable entries, in which case it is assumed to be the value for the keyword, SERVER. A completely corrupted ConnectOptions entry will therefore probably try to connect to an invalid server.