Oct 1, 2013

The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'language'

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
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT)
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 'language'.
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 language of natively complied stored procedures.

Solution: We can get list of all supported languages of natively compiled stored procedure by following script:

SELECT Name,Alias FROM sys.syslanguages

And specify your language in the atomic block of natively compiled stored procedure. 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

No comments:

Post a Comment