Crashing CLR on SQL Server 2014 (windows 2012R2)

Question

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

Answer 1

The problem is a locale conflict between the Windows OS and SQL Server (specifically the database where the Assembly is loaded). You can run the following query to see what they are both set to:

SELECT os_language_version,
       DATABASEPROPERTYEX(N'{name of DB where Assembly exists}', 'LCID') AS 'DatabaseLCID'
FROM   sys.dm_os_windows_info;

If they are different then you can definitely get some "odd" behavior, such as what you are seeing. The issue is that:

• SqlString includes more than just the text itself: it includes the default collation of the database in which the assembly exists. The collation is comprised of two pieces of information: the locale info (i.e. LCID), and the comparison options (i.e. SqlCompareOptions) which detail the sensitivity to case, accents, kana, width, or everything (binary and binary2).
• String operations in .NET, unless explicitly given a locale, use the locale info of the current thread, which is set in Windows (i.e. the Operating System / OS).

The conflict usually occurs when referencing a SqlString parameter without using .Value or .ToString() such that it does an implicit conversion to SqlString. In that case it would cause an exception saying that the LCIDs do not match.

There are apparently other scenarios, such as performing (some / all?) string comparisons, including when using Regex as this case shows (though so far I have not been able to reproduce this).

Some ideas for fixes:

Ideal (expectations will always be met regarding how the comparisons work):

• Change either the Windows or SQL Server LCID (default language) so that both match

Less than ideal (the behavior of the Windows locale might not be the same rules for equality and sorting and so there could be unexpected results):

• Use the .ToString method or .Value property, which both return the string without the SQL Server LCID so the operations will all be using the OS LCID.

Might help:

• Maybe use SqlChars instead of SqlString as it does not bring along the LCID and collation info from SQL Server
• Specify that culture doesn't matter via StringComparison.InvariantCulture:
  • String.Compare(string, string, StringComparison.InvariantCulture) or String.Compare(string, string, StringComparison.InvariantCultureIgnoreCase)
  • For Regex, specify RegexOptions.CultureInvariant

Answer 2

Updated..

The localization is different between the SQL Engine and the window Server as @srutzky points out:

os_language_version SqlServerLCID
1033                1039

The following change to the code - setting the option RegexOptions.CultureInvariant gets around the error. The unchanged code will not crash SQL Server 2012 on Windows Server 2012R2 with the same language settings but does so on SQL Server 2014.

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