Django change upgrade MSSQL database

I have a Django app that was running without any problem with SQL server 2008, I have an issue like this after I upgraded the MS SQL server 2008 into 2019. I didn’t change anything in the code but when I’m calling the DB, I face this error:

42000′, ‘[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find either column “dbo” or the user-defined function or aggregate dbo.REGEXP_LIKE”, or the name is ambiguous.
(4121) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
Statement(s) could not be prepared. (8180)

Answer

The issue was about Regex on Django and SQL server. SQL server doesn’t support Regex. So, You have to add an assembly to patch the SQL server. To do so, You need to have a Class library in .netframework to create a .dll file. Here is the code for the class library:

using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    private const RegexOptions DefaultRegExOptions =
        RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline;

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlInt32 REGEXP_LIKE(SqlString input, SqlString pattern, SqlInt32 caseSensitive)
    {
        RegexOptions options = DefaultRegExOptions;
        if (caseSensitive==0)
            options |= RegexOptions.IgnoreCase;

        return Regex.IsMatch(input.Value, pattern.Value, options) ? 1 : 0;
    }
}

Then you need to configure your DB using this SQL script:

-- Use the proper database
use [DBNAME]
GO

-- Enable CLR in this database
EXEC sp_configure 'show advanced options', 1;
GO
EXEC sp_configure 'clr strict security',0;
GO
RECONFIGURE;
GO
EXEC sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'clr strict security',1;
GO

-- Create the assembly in the database from the path can this be done via a file path?
CREATE ASSEMBLY regex_clr from ' your DLL file location, for example: C:mehdiregex_clr.dll' WITH PERMISSION_SET = SAFE
GO

-- Pull in the User Defined Function from the assembly
create function REGEXP_LIKE
(
    @input nvarchar(4000),
    @pattern nvarchar(4000),
    @caseSensitive int
) 
RETURNS INT  AS 
EXTERNAL NAME regex_clr.UserDefinedFunctions.REGEXP_LIKE
GO

then your db would work with REGEX properly.