1

I have an ABAP class with various methods for reading from / writing to a remote Microsoft SQL Server 2005 instance.

Everything works as expected. Now I've been advised to add a SQL Server table hint (READPAST) to a SELECT query, for safety reasons (it should be a measure against deadlocks - I'm far from a SQL expert).

Sadly I can't make it work. This is my Native SQL block and as it is it works:

EXEC SQL.
  OPEN ritc FOR
    SELECT FIELD1,
           FIELD2,
           FIELD3,
           FROM MY_TABLE
           WHERE FIELD1 <= :lv_variable1
             AND FIELD3 =  :c_constant
ENDEXEC.

If I try adding WITH(READPAST) right after FROM MY_TABLE, I get this error: You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Fair enough: I tried adding this command right before the OPEN ritc line:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This elicits a different error: at the first FETCH command after this block I get an error message saying the cursor ritc exists and it's already opened.

At this point I'm not even sure I can add table hints to a Native SQL block?

Any suggestions? Thanks in advance.

MacThePenguin
  • 128
  • 4

2 Answers2

0

Maybe you needed the BEGIN TRANSACTION statement?

(You also need an END TRANSACTION statement in another ABAP block.)

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • Maybe it would (I haven't tried), but I don't want to use NOLOCK: I want to use READPAST, it's quite a bit different. Thanks for your suggestion anyway. – MacThePenguin Nov 04 '10 at 16:52
  • Thanks for the update. I've googled for a while and I haven't found a way to set the isolation level from the connection string. Actually, I have found this post here on stackoverflow that says it can't be done: http://stackoverflow.com/questions/3303782/can-i-set-the-isolation-level-in-the-connection-string – MacThePenguin Nov 04 '10 at 17:17
0

I think the SET needs to be done after the OPEN.

EXEC SQL.
  OPEN ritc FOR
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SELECT FIELD1,
           FIELD2,
           FIELD3,
           FROM MY_TABLE WITH (READPAST)
           WHERE FIELD1 <= :lv_variable1
             AND FIELD3 =  :c_constant
ENDEXEC.
Jason Cumberland
  • 992
  • 7
  • 11