CLR Call In Functions

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

CLR Call In Functions

Postby mdavide » Fri Jun 06, 2008 3:30 pm

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
mdavide
 
Posts: 3
Joined: Tue Jan 10, 2006 3:31 pm

Postby Ben Pinchen » Mon Jun 09, 2008 2:21 pm

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/vi ... php?t=4397
Ben Pinchen
 
Posts: 157
Joined: Thu Dec 28, 2006 1:21 pm
Location: Red Gate


Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests