Progress
DataServer for
Microsoft SQL Server
Guide
Using SQL Server™ and DataServer Options
This section provides information on using various ODBC and DataServer options.
To avoid using the SQL Server™ transaction manager, specify -Dsrv AUTOCOMMIT,1. This option exercises the Progress client’s local before-image mechanism to manage transactions. This creates a transaction from each SQL statement that the DataServer issues. Progress emulates the logical transaction on behalf of the application using the local before-image mechanism. This connection technique is useful in multi-database applications.
NOTE: Progress does not guarantee crash recovery when it uses the local before-image mechanism to emulate transactions.Connection Problems: The PRGRS_CONNECT Option
The PRGRS_CONNECT option allows you to pass ODBC-specific information to the ODBC driver. This option has the following syntax:
The connection string is separated from the option by a comma (,) and ends with a semicolon (;).
Use the PRGRS_CONNECT option in the following cases:
- To connect to a data source whose name is not allowed by Progress; for example, a name that includes blank spaces, ampersands (&), commas (,), and/or carets (^). In the connection string, pass the following characters rather than the unallowed characters. The driver resolves the passed characters to the unallowed character:
- To establish complex connections that require more than the Physical Database Name (-db), User ID (-U), and Password (-P) parameters. In all cases, the values must not be space delimited and must be passed in a single connection string. For example, the following connection string sets the user ID and password for the server and user ID and password for the data source:
For more information and syntax examples, see the "Special Connection Issues" section in Connecting the DataServer."
Key-Buffer Size: The PRGRS_IDBUF Option
The PRGRS_IDBUF option sets the size of the key buffers. Generally, a default of 25 is sufficient. Key buffers are used with all nonlookahead cursors.
Locking Error Messages: The PRGRS_LOCK_ERROR Option
DataServer for SQL Server™ identifies and handles conditions and errors. However, the PRGRS_LOCK_ERROR option lets you control how your application reacts if it encounters an error that is actually a lock problem when accessing a data source. Use this option to pass the native error number to the DataServer so that it handles this error as it would a Progress lock problem; that is, the DataServer waits and retries, rather than halting the application:
Large Rows: The PRGRS_MINBUF Option
Some data rows can be very large; for example, in a SQL Server™ database, rows often have large fields such as IMAGE and MEMO. The ODBC protocol specifies a dynamic buffer allocation process for handling large rows that do not initially fit into clients’ buffers; however, some drivers do not yet follow the correct ODBC protocol and do not handle these large rows correctly. Use the -Dsrv PRGRS_MINBUF,size option to force a minimum buffer size. For example, -Dsrv PRGRS_MINBUF,15000 enables the DataServer to handle 15K rows even with drivers that fail to follow the ODBC protocol.
NOTE: It is often difficult to determine when there is a buffer size problem and how to choose the correct value for PRGRS_MINBUF. Be careful when using this option.SQL Statements Cache: The PRGRS_PREPCACHE Option
The DataServer keeps a cache of prepared SQL statements that it reuses with new parameters. This mechanism improves the DataServer performance. You can use the PRGRS_PREPCACHE option to manage this cache in two ways:
- The SQL Server™ drivers are capable of re-using prepared statements and should do so whenever possible. However, using -Dsrv PRGRS_PREPCACHE,0 instructs the DataServer to re-prepare each SQL statement.
- Use the PRGRS_PREPCACHE option to control the size of the cache. The default cache size is 20 statements. You can increase the size for large applications that reuse many queries. The maximum size depends on the amount of resources you have available.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |