CLR Call In Functions

[mdavide]

I am currently having an issue when attempting to package a function that contains a call to a CLR function. When Packager creates it, it scripts the following (Incorrect syntax and all)

CREATE FUNCTION [dbo].[clrUdfCharListToTable] (@str [nvarchar] (0), @Delim [nvarchar] (1)=N',')
RETURNS TABLE (
[iRowId] [int] NULL,
[cValue] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
WITH EXECUTE AS CALLER
EXTERNAL NAME [CFAST CLR Functions].[CX_CLR_Funct.StringListToTable].[clrUdfCharListToTable]
GO

Here are the parsing errors:

g 1001, Level 15, State 1, Procedure clrUdfCharListToTable, Line 1
Line 1: Length or precision specification 0 is invalid.
Msg 319, Level 15, State 1, Procedure clrUdfCharListToTable, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Procedure clrUdfCharListToTable, Line 6
Incorrect syntax near the keyword 'EXTERNAL'.


When scripted through SSMS, (and no incorrect syntax), it looks like this:

CREATE FUNCTION [dbo].[clrUdfCharListToTable](@str [nvarchar](max), @Delim [nvarchar](1))
RETURNS TABLE (
[iRowId] [int] NULL,
[cValue] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CFAST CLR Functions].[CX_CLR_Funct.StringListToTable].[clrUdfCharListToTable]
GO

Any insight as to why SQL Packager is incorrectly scripting the object?

Thanks,

Mike

[Ben Pinchen]

I think that you maybe experiencing an issue that we're aware of. SQL Packager 5.x uses the SQL Compare 5.x engine which has an issue with the nvarchar (max) syntax. This has been fixed in the SQL Compare 6 engine but is yet to be implemented in SQL Packager. We are updating our applications to coincide with the release of SQL Server 2008 (Q3). So, it is likely that when we work on SQL Packager will we update it to use the SQL Compare 6 engine which will resolve this issue. You can see a few more details in the following post - http://www.red-gate.com/MessageBoard/viewtopic.php?t=4397