Sep 30, 2013

The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'transaction isolation level'

If we will create a very simple stored natively compiled procedure in the sql server 2014:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(LANGUAGE = N'us_english')
    SELECT 'Simple Procedure'
END

We may get following error message:

Msg 10784, Level 15, State 1, Procedure uspGetEmployee, Line 17
The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'transaction isolation level'.
Msg 102, Level 15, State 1, Procedure uspGetEmployee, Line 19
Incorrect syntax near 'END'.
Msg 10783, Level 15, State 1, Procedure uspGetEmployee, Line 19
The body of a natively compiled stored procedure must be an ATOMIC block.

Cause: It is necessary to specify the transaction isolation level while creating the natively compiled stored procedure.

Solution: Specify isolation level of natively compiled stored procedures. It can be SNAPSHOT, REPETABLEREAD or SERIALIZABLE. For example:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
   
    SELECT 'Simple Procedure'


END

1 comment:

  1. I have a quick question:
    Apart from Isolation level and language, is there any other option that we can include in WITH clause of BEGIN ATOMIC statement?

    ReplyDelete