Sep 25, 2013

Conversion from '' to '' is not yet implemented with natively compiled stored procedures

In the natively complied stored procedure if we will try to cast or covert data types like char, nchar, varchar or nvarchar in the sql server 2014 we may get error. For example:

CREATE PROCEDUREuspCastData(
     @vcParam AS VARCHAR(50)
)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
    
     DECLARE @var AS NVARCHAR(50)

     SET @var = CAST(@vcParam AS NVARCHAR(50))

END

We may get error message something like:
Conversion from 'varchar(50)' to 'nvarchar(50)' is not yet implemented with natively compiled stored procedures.

Cause: It is limitation of sql server 2014. We cannot type cast varchar to nvarchar etc.

Solution: Since it is limitation so we can type case using cast or covert function. So we must have to use same data types. Change the data type in the source tables etc from where you are getting the data of different types. For Examples:

CREATE PROCEDUREuspCastData(
     @vcParam AS NVARCHAR(50)
)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
    
     DECLARE @var AS NVARCHAR(50)

     SET @var = @vcParam


END

No comments:

Post a Comment