I have this small CLR that does a RegEX function on a string in columns.
When running on SQL Server 2014 (12.0.2000) on Windows Server 2012R2 the process crashes with
Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
and gives a stack dump if I do
select count (*) from table where (CLRREGEX,'Regex')
but when I do
select * from table where (CLRREGEX,'Regex')
it returns the rows.
Works perfectly on same SQL Server build running on Windows 8.1 .
Any ideas?
-- Edit It is as simple as it can be
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes; //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server; //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline;
[SqlFunction]
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
return SqlBoolean.False;
return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
}
}
So by little changes this works now: Main lesson in C# seems to be the same as in TSQL beware of implicit data conversion.
using System;
using System.Text;
using System.Data.SqlTypes; //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server; //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.CultureInvariant;
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.Read)]
public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
return SqlBoolean.False;
string sqldata = input.ToString();
string regex = pattern.ToString();
return Regex.IsMatch(sqldata, regex);
}
SqlFunction
method marked asIsDeterministic=true
? Is the assembly marked asSAFE
?[SqlFunction]
attribute. Is that the exact code? I don't think that would compile. The Framework version 2.0 / 3.0 / 3.5 distinction is a non-issue as you are using 4.0 / 4.5 / 4.5.x / etc or whatever is on that server since you are on SQL Server 2014 which is bound to CLR version 4. Is the server showing the problem 32-bit? How much memory does it have compared to the other servers? And have you checked the SQL Server logs just after getting that error?MatchTimeout
property. But I don't think that is really the issue either if you are only passing in 5 chars max. It is possible that this one machine has a corrupted install of the .NET Framework, and that can be repaired once trout fishing activities have ceased ;-). Also,[0-9].*
is simple but also inefficient since it matches all chars, if any, after the first digit; using just[0-9]
for anIsMatch
is better.DataAccessKind
toRead
? That just slows it down and you aren't doing any data access. Also, I do realize that it seems to be working now, but I would be cautious with using theToString()
method as opposed to theValue
property as I don't think ToString handles encodings properly, or something like that. What is your databases collation set to? Of course, I just re-read one of your comments above and see that the column is VARCHAR instead of NVARCHAR. Does that field have a different collation than the database?