Sep 22, 2013

Object '' is not a memory optimized table and cannot be accessed from a natively compiled stored procedure

Suppose we have created table by following script in sql server 2014:

CREATE TABLEtblEmp(
     ntEmpID BIGINT PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)

Now we want to create a natively compiled stored procedure:

CREATE PROCEDUREuspGetEmpInfo
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
     SELECT vcName,moSalary FROM dbo.tblEmp
END

Then we may get error message something like:

Msg 10775, Level 16, State 1, Procedure , Line 5
Object '' is not a memory optimized table and cannot be accessed from a natively compiled stored procedure.

Cause: In the natively compiled stored procedure we can only use memory optimized table.

Solution: Change the tblEmp to memory optimized table. For example:

Step 1: Drop the existing table:

DROP TABLE dbo.tblEmp

Step 2: Create memory optimized table

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL,
     vcName VARCHAR(50),
     moSalary MONEY,
     CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)
WITH(MEMORY_OPTIMIZED =ON)


Now we can reference above table in the natively complied stored procedure. 

No comments:

Post a Comment