Sep 26, 2013

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
)


No comments:

Post a Comment