ABAP: using SQL Server table hints in a Native SQL block

Question

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.

Answer 1

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.