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

SET LANGUAGE failed because '' is not an official language name or a language alias on this SQL Server

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

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

We may get following error message:

SET LANGUAGE failed because '' is not an official language name or a language alias on this SQL Server.

Cause: Language we have specified while creating natively compiled stored procedure is not supported by sql server 2014.

Solution: We can get the list of all sported languages in sql server 2014 by following script:

SELECT name,alias FROM sys.syslanguages

List of supported languages:
Name
Alias
us_english
English
Deutsch
German
Français
French
日本語
Japanese
Dansk
Danish
Español
Spanish
Italiano
Italian
Nederlands
Dutch
Norsk
Norwegian
Português
Portuguese
Suomi
Finnish
Svenska
Swedish
čeština
Czech
magyar
Hungarian
polski
Polish
română
Romanian
hrvatski
Croatian
slovenčina
Slovak
slovenski
Slovenian
ελληνικά
Greek
български
Bulgarian
русский
Russian
Türkçe
Turkish
British
British English
eesti
Estonian
latviešu
Latvian
lietuvių
Lithuanian
Português (Brasil)
Brazilian
繁體中文
Traditional Chinese
한국어
Korean
体中文
Simplified Chinese
Arabic
Arabic
ไทย
Thai
norsk (bokmål)
Bokmål
name
alias

So we can specify either language name or alias name. 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

Or

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


END

Sep 26, 2013

The table type '' is not a memory optimized table type and cannot be used in a natively compiled stored procedure

If will use table type in the natively complied stored procedure in the sql server 2014:

CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)

Go
CREATE PROCEDUREuspGetEmp
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
     DECLARE @tblData ASdbo.tblEmp

     SELECT vcName,moSalary FROM @tblData

END


We may get following error message:

Msg 41323, Level 16, State 1, Procedure uspGetEmp, Line 39
The table type 'dbo.tblEmp' is not a memory optimized table type and cannot be used in a natively compiled stored procedure.

Cause: We can use normal table type in the natively complied stored procedures.

Solution: Create memory optimized table type. For example:

DROP TYPE tblEmp

CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)

WITH(MEMORY_OPTIMIZED =ON)

The table type '' is a memory optimized table type and cannot be used outside a natively compiled stored procedure

If we will use the memory optimized type in the stored procedure in sql server 2014:

CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

GO
CREATE PROCEDUREgetEmp
AS
     DECLARE @tblData AStblEmp
     SELECT * FROM @tblData

We may get following error message:

Msg 41324, Level 16, State 1, Procedure getEmp, Line 26
The table type 'tblEmp' is a memory optimized table type and cannot be used outside a natively compiled stored procedure.

Cause: Memory optimized table can be used only in the natively complied stored procedure.

Solution:

1. Use memory optimized table type in the natively complied stored procedure. For examples:

CREATE PROCEDUREuspGetEmp
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
     DECLARE @tblData ASdbo.tblEmp

     SELECT vcName,moSalary FROM @tblData

END

2. If change the type to not memory optimized. For examples:

CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)


The feature 'table-valued parameters' is not supported with natively compiled stored procedures

If we will pass the table variable in the natively complied stored procedure in the sql server 2014:

--Creating memeory otimized type
CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

--Creating memeory optimized table
CREATE TABLE[dbo].[tblEmp]
(
     [ntEmpID] [bigint] NOT NULL,
     [vcName] [varchar](50) NULL,
     [moSalary] [money] NULL

 PRIMARY KEY NONCLUSTERED HASH
(
     [ntEmpID]
)WITH ( BUCKET_COUNT= 1024)
)WITH ( MEMORY_OPTIMIZED= ON , DURABILITY = SCHEMA_AND_DATA )


--Passing table varaible in the nativly compiled stored procedure
CREATE PROCEDURE  getEmp(
     @tblEmp AS tblEmpType READONLY
)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
     INSERT INTO [tblEmp]
     SELECT * FROM @tblEmp
END

We may get following error message:

Msg 10772, Level 16, State 22, Procedure getEmp, Line 33
The feature 'table-valued parameters' is not supported with natively compiled stored procedures.

Cause: Natively complied stored procedures in the sql server 2014 doesn’t supports table variable as a parameter.

Solution: No need to use natively complied stored procedure to insert in the memory optimized table.

--Creating a table type
CREATE TYPEtblEmpType AS TABLE (
     ntEmpID BIGINT NOT NULL PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)

Go

--Passing table variable as parameter to insert in the
--memory optimized tables
CREATE PROCEDUREuspAddEmp(
     @tblEmp AS tblEmpType READONLY
)
AS
BEGIN

     INSERT INTO [tblEmp]
     SELECT * FROM @tblEmp

END

Script to get the list of all natively compiled stored procedures in sql server 2014

We can get the list of all natively complied stored procedures in the current database by following sql query:

SELECT
     SCHEMA_NAME([schema_id]) AS SchemaName,
     name AS ProcedureName
FROM sys.procedures
WHERE OBJECTPROPERTY([object_id],'Isschemabound') = 1

Sample Output:

SchemaName
ProcedureName
dbo
uspGetEmployee
dbo
uspAddSkills
dbo
uspGetType


As we know a stored procedure can be schema bound if and only if it is natively complied stored procedures. So we are using this feature to get the list.  Here sys.procedures is system view. 

Script to get the list of all memory optimized tables in sql server 2014

We can get the list of all memory optimized tables in the current database by following sql query:

SELECT
     SCHEMA_NAME([schema_id]) AS SchemaName,
     name AS TableName
FROM sys.tables
WHERE is_memory_optimized = 1

Sample Output:

SchemaName
TableName
dbo
tblEmployee
dbo
tblSkills
dbo
tblType



Here sys.tables is a system view.