Oct 1, 2013

The body of a natively compiled stored procedure must be an ATOMIC block

If we will create a natively compiled stored procedure in sql server 2014 by following script:

CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
     SELECT 'Simple Procedure'

We may get following error message:

Msg 10783, Level 15, State 1, Procedure uspGetEmployee, Line 17
The body of a natively compiled stored procedure must be an ATOMIC block.

Cause: It is necessary that a natively complied stored procedure must have exactly one atomic block.

Solution: Atomic block guarantee the atomic execution of procedure. So include atomic block. For example:

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

     SELECT 'Simple Procedure'

No comments:

Post a Comment