Synchronzation of Assembly/UDF with parameter nvarchar(MAX)

edited December 20, 2006 6:39AM in SQL Compare Previous Versions
If have a CLR scalar Function / UDF wich I can not synchronize to another database.

I got the error:

The following error message was returned from the SQL Server:

[1001] Line 1: Length or precision specification 0 is invalid.
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.
Incorrect syntax near the keyword 'EXTERNAL'.

Do you have any advice for me?
thanks,
Olaf

The following SQL command caused the error:

CREATE FUNCTION [dbo].[fn2_regexIsMatch] (@expressionToValidate [nvarchar] (0), @regularExpression [nvarchar] (0))
RETURNS [bit]
WITH EXECUTE AS CALLER
EXTERNAL NAME [DETECON_safe].[UserDefinedFunctions].[clrs_f_regexIsMatch]

The following messages were returned from the SQL Server:

[5701] Changed database context to 'DETECON'.
[5703] Changed language setting to us_english.
[0] Creating CLR assemblies
[0] Creating [dbo].[fn2_regexIsMatch]


The auto-deployed SQL -Sript for the UDF is:
USE [DETECON]
GO
/****** Object: UserDefinedFunction [dbo].[fn2_regexIsMatch] Script Date: 10/12/2006 14:00:59 ******/
CREATE FUNCTION [dbo].[fn2_regexIsMatch](@expressionToValidate [nvarchar](max), @regularExpression [nvarchar](max))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DETECON_safe].[UserDefinedFunctions].[clrs_f_regexIsMatch]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn2_regexIsMatch'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'clrs_f_regexIsMatch.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn2_regexIsMatch'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=24 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn2_regexIsMatch'

The c# code is:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
/// <summary>
/// Regex Funktionen
/// </summary>
public partial class UserDefinedFunctions
{
/// <summary>
/// eine Zeichenfolge wird auf Regex Match Kode geprüft
/// Regex-Optionen: RegexOptions.CultureInvariant | RegexOptions.IgnoreCase
///
/// $Archive: /Intranet/3 - CLR Intranet/DETECON_safe/clrs_f_regexIsMatch.cs $
/// $Revision: 2 $
/// $Date: 12.10.06 13:46 $
/// $Author: SqlAdmW $
/// </summary>
/// <param name="expressionToValidate"></param>
/// <param name="regularExpression"></param>
/// <returns>1 = Match, sonst 0, NULL = wenn einer der Parameter NULL ist</returns>
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess=DataAccessKind.None
,IsDeterministic=true
,IsPrecise=true
,SystemDataAccess=SystemDataAccessKind.None
,Name="fn2_regexIsMatch"
)
]
public static SqlBoolean clrs_f_regexIsMatch([SqlFacet(MaxSize = -1)]SqlString expressionToValidate, [SqlFacet(MaxSize = -1)]SqlString regularExpression)
{
if (expressionToValidate.IsNull || regularExpression.IsNull)
{
return SqlBoolean.Null;
}
else
{
Regex regex = new Regex(regularExpression.Value.ToString(),RegexOptions.CultureInvariant | RegexOptions.IgnoreCase);
return regex.IsMatch(expressionToValidate.Value.ToString());
}
}
};

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello Olaf,

    On the surface, this seems to be more of an issue with SQL Server. The definition of the object you're trying to migrate specifies a zero-lenth NVARCHAR column and the SQL Server will not accept that. Admittedly, a zero-length column doesn't make a lot of sense. I think the easiest thing to do would be to fix this object so that it has a discernable length.

    I think that this may also be a compatibility issue. Possibly SQL Server 2005 can accept this data length specification and you're attempting to run the script on a SQL 2000 server?
  • Hello Brian,

    I am using only SQL Server 2005 not 2000.

    I'd like explaining more in detail my steps (all codings are in my first posting):

    1. I used the Visual Studio 2005 (VS) to develop the c# CRL function:
    ...
    [Microsoft.SqlServer.Server.SqlFunction(
    DataAccess=DataAccessKind.None
    ,IsDeterministic=true
    ,IsPrecise=true
    ,SystemDataAccess=SystemDataAccessKind.None
    ,Name="fn2_regexIsMatch"
    )
    ]
    public static SqlBoolean clrs_f_regexIsMatch([SqlFacet(MaxSize = -1)]SqlString expressionToValidate, [SqlFacet(MaxSize = -1)]SqlString regularExpression)
    {
    ...

    There are the definitions of the parameters ([SqlFacet(MaxSize = -1)]SqlString ...) to use the SQL data type nvarchar(max) in the T-SQL interface (UDF). (In this case I am not able to use the default data type mapping between CLR and SQL Server data types of VS.)

    2. I used the auto-deploy function of VS.

    3. I scripted the T-SQL UDF (fn2_regexIsMatch) with SSMS to check the auto-deployed UDF.
    ...
    /****** Object: UserDefinedFunction [dbo].[fn2_regexIsMatch] Script Date: 10/12/2006 14:00:59 ******/
    CREATE FUNCTION [dbo].[fn2_regexIsMatch](@expressionToValidate [nvarchar](max), @regularExpression [nvarchar](max))
    RETURNS [bit] WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [DETECON_safe].[UserDefinedFunctions].[clrs_f_regexIsMatch]
    GO
    ...
    The parameters are all of type nvarchar(max) not nvarchar(0)!
    (I have tested this function with SQL Server, it is running.)

    4. Then I used SQL-Compare to transfer the code of the UDF to another SQL Server 2005 and I got the Error message with SQL-Compare:
    ...
    [1001] Line 1: Length or precision specification 0 is invalid.
    ...
    CREATE FUNCTION [dbo].[fn2_regexIsMatch] (@expressionToValidate [nvarchar] (0), @regularExpression [nvarchar] (0))
    ...


    What do you think?

    Thanks,
    Olaf
  • Is there no hint available for us?
  • Hi,

    Sorry you have been kept in the dark for so long.

    The development team have confirmed this as a bug with SQL Compare. It is currently scheduled to be fixed for version 6 of the software.
    Chris
  • Hi Chris,
    thank you for your information!
    Many regards
    Olaf
Sign In or Register to comment.