Native Compiled Functions not working?

guruper2

I have several function native compiled functions in my SQL Server 2016 SP1 database. I get an error on all of them trying to sync between my database and source control system. The same goes for SQL Source Control. Is SQL Compare and SQL Source Control not compatible with native compiled functions and procedures?

Here is a sample of an SQL with syntax error (according to SQL Compare). I works fine on SQL Server 2016:

CREATE FUNCTION [dbo].[cf_GetWordID] (@WordValue varchar(200))
RETURNS int
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

DECLARE @WordID int=0;

IF @WordValue!=''
SET @WordID=ISNULL((SELECT WordID FROM dbo.WordList WHERE WordValue=@WordValue),-1)

RETURN (@WordID)
END
GO

David E

Hi,

Thanks for you posts. I can confirm that while SQL Compare works with native compiled stored procedures, we do not currently support this option for functions.

I've logged this as a feature request with reference SC-9266. Unfortunately I can't confirm when we might be working on this, but we will keep this forum thread updated with any further progress.

Regards,
David

guruper2

I find it strange that SQL Server 2016 is not fully supported 1 year after the RTM launch. You also state compatibility in the marketing material. It says: "What can I compare, synchronize, and deploy?" - "SQL Server 2005, 2008, 2012, 2014, 2016, SQL Azure, & SQL Server on Amazon RDS databases". Not completely true then if I can't choose all objects. Do you have a list of known unsupported features in SQL 2016? I just have to take this into account when I design my systems so I still can use SQL Compare. I've used the Red Gate Products since the early beginning, but this is the first time I'm stuck.

David E

I understand your frustration with this, we are currently working in a development cycle of ensuring that there are features for both adopters of the latest versions of SQL Server and those that will benefit the wider community in each release. We are still working on SQL Server 2016 support, as I type this we are working on support for Temporal Tables which is the most requested feature for 2016.

At present we are aware of deployment issues with:
- Temporal tables
- Native compiled functions
- Keys associated with Always Encrypted functionality

If there are other features that you are encountering issues with we would like to hear about them. We want to make sure we are targeting the most valuable features first.

sam.blackburn

We've now released SQL Compare 13.1.6.5463 to our frequent updates channel, which supports natively compiled functions. Does this work as expected?