Kbase P104122: Getting errors when executing a MS SQL Server stored procedure containing a cursor against a linked
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/05/2005 |
|
Status: Unverified
FACT(s) (Environment):
MS SQL 2000
MERANT 3.60 32-BIT Progress SQL-92
DataDirect 4.20 32-Bit OpenEdge SQL92 10.0B
DataDirect 4.10 32-Bit Progress SQL92 9.1E
SYMPTOM(s):
Getting errors when executing a MS SQL Server stored procedure against a linked Progress database
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor. [SQLSTATE 01000]
Syntax error (7587)
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].
The stored procedure contains a cursor defined as follows:
DECLARE c_Customer CURSOR FOR SELECT CustNum, Name FROM dbSports2000..pub.customer WHERE Country = @cCountryName
The transaction isolation level is READ UNCOMMITTED
The stored procedure does not perform any update to the Progress dababase.
CAUSE:
The cursor is an updatable cursor on a linked server which normaly requires a higher transaction isolation level than the READ UNCOMMITTED.
FIX:
Since there is no need to make any update from the stored procedure, the transaction isolation level does not have to be increased.
Instead, add the INSENSITIVE keyword when defining the cursor against the linked Progress database.
This allows to define a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
For example, assuming that dbSports2000 is the Progress sports2000 database linked into the MS SQL Server database, the cursor definition becomes:
DECLARE c_Customer INSENSITIVE CURSOR FOR SELECT CustNum, Name FROM dbSports2000..pub.customer WHERE Country = @cCountryName