Synchronzation of Assembly/UDF with parameter nvarchar(MAX)

Olaf Pietsch DETECON

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());
}
}
};

Brian Donahue

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?

Olaf Pietsch DETECON

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

Olaf Pietsch DETECON

Is there no hint available for us?

Chris Auckland

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.

Olaf Pietsch DETECON

Hi Chris,
thank you for your information!
Many regards
Olaf