AzureData WarehousingMicrosoft Business IntelligenceSQL Server

SQL Server CLR Function on Azure SQL Server Managed Instance

By 24 October 2020No Comments

When you need to deploy an assembly (.net DLL) SQL Server CLR Function on Azure SQL Server Managed Instance, you have to do it slightly differently. I’ll show you how to do this with the least amount of effort!

On a locally hosted or native installation of SQL Server, you would reference the assembly locally. i.e.

CREATE ASSEMBLY HelloWorld
FROM '<system_drive>:\Program Files\Microsoft SQL Server\100\Samples\HelloWorld\CS\HelloWorld\bin\debug\HelloWorld.dll'
WITH PERMISSION_SET = SAFE;

But with Azure SQL Server Managed Instance you cannot reference an assembly file from a local file.

Msg 40559, Level 16, State 1, Line 10
File based statement options are not supported in this version of SQL Server.

You can, however, create the assembly reference using a HEX representation of the bits in a raw binary file. i.e.

CREATE ASSEMBLY HelloWorld  
    FROM 0x4D5A900000000000  
WITH PERMISSION_SET = SAFE;

But how do you easily convert the DLL to a HEX string of bytes that will play nicely with SQL Server and SSMS? You can use a little C# and LINQPad. Or Visual Studio Code will also work.

Open LINQPad and create a new C# Program and paste in the code, pointing the file to your DLL. This code will read the DLL and create a new file with a nicely formatted HEX string ready to paste into SQL Server Management Studio.

List<string> SplitToLines(string stringToSplit, int maximumLineLength)
{
	var words = stringToSplit.Split(' ').Concat(new[] { "" });
	words = words.Skip(1)
				 .Aggregate(words.Take(1).ToList(), (a, w) =>
					{
						var last = a.Last();
						while (last.Length > maximumLineLength)
						{
							a[a.Count() - 1] = last.Substring(0, maximumLineLength) + @"\";
							last = last.Substring(maximumLineLength);
							a.Add(last);
						}
						var test = last + " " + w;
						if (test.Length > maximumLineLength)
						{
							a.Add(w);
						}
						else
						{
							a[a.Count() - 1] = test;
						}
						return a;
					});
					
	var linesList = words.ToList();
	linesList[0] = "0x" + linesList[0];

	return linesList;
}

static string GetHexString(string assemblyPath)
{
	if (!Path.IsPathRooted(assemblyPath))
		assemblyPath = Path.Combine(Environment.CurrentDirectory, assemblyPath);

	StringBuilder builder = new StringBuilder();


	using (FileStream stream = new FileStream(assemblyPath,
		  FileMode.Open, FileAccess.Read, FileShare.Read))
	{
		int currentByte = stream.ReadByte();
		while (currentByte > -1)
		{
			builder.Append(currentByte.ToString("X2", System.Globalization.CultureInfo.InvariantCulture));
			currentByte = stream.ReadByte();
		}
	}

	return builder.ToString();
}

void Main()
{
	// get the file from the same location as the linqpad script is stored.
	//
	var scriptPath = Path.GetDirectoryName(Util.CurrentQueryPath);

	// enter your dll name
	//
	var dllName = "Fastenshtein.dll";

	// enter your hex output
	//
	var hexOutput = "Fastenshtein.hex";

	var hexString = GetHexString(@$"{scriptPath}\{dllName}");
	var lines = SplitToLines(hexString, 256);

	File.WriteAllLines(@$"{scriptPath}\{hexOutput}", lines);

	foreach (var s in lines)
		s.Dump();

	"Finished...".Dump();
}

// Define other methods, classes and namespaces here

In our example, we are using the Fastenhien library, which is a .NET CLR implementation of a Levenshtein algorithm used for Fuzzy Matching strings. It’s a LOT faster as a CLR implementation than as a native SQL Server Function.

Now you take the hex file content and construct your SQL Command to import it to SQL Server Managed Instance.

DROP FUNCTION IF EXISTS  dbo.ufn_fast_levenshtein
GO

DROP ASSEMBLY IF EXISTS FastenshteinAssembly
GO

PRINT N'Creating CLR assemblies';
GO
CREATE ASSEMBLY FastenshteinAssembly AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000\
504500004C0103000290825B0000000000000000E00022200B013000001000000006000000000000C22E0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000\
702E00004F00000000400000D803000000000000000000000000000000000000006000000C000000382D00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000\
C80E0000002000000010000000020000000000000000000000000000200000602E72737263000000D8030000004000000004000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001600000000000000000000000000004000004200000000000000000000000000000000\
A42E0000000000004800000002000500B0220000880A0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300400B700000001000011026F1000000A2D02162A026F1000000A8D150000010A160C2B080608081758250C9E0806\
8E6932F2026F1000000A036F1000000A3208036F1000000A2B06026F1000000A0B160D2B6209130409130503096F1100000A13061613072B40110413081105130406110794130511060211076F1100000A2E1A110811042F0411081304110511042F041105130411041758130406110711049E1107175813071107026F100000\
0A32B60917580D0907329A06068E691759942A9202281200000A02037D0100000402027B010000046F1000000A8D150000017D020000042A32027B010000046F1000000A2A00000013300400B200000002000011027B020000048E2D07036F1000000A2A160A2B0D027B0200000406061758250A9E06027B020000048E6932E8\
160B2B67070C070D03076F1100000A13041613052B42081306090C027B020000041105940D1104027B0100000411056F1100000A2E121106082F0311060C09082F02090C0817580C027B020000041105089E1105175813051105027B010000046F1000000A32AF0717580B07036F1000000A3290027B02000004027B02000004\
8E691759942A0000133004009A00000003000011036F1000000A2D07026F1000000A2A036F1000000A8D150000010A160B2B080607071758250B9E07068E6932F2160C2B58080D08130402086F1100000A13051613062B3709130711040D06110694130411050311066F1100000A2E141107092F0311070D1104092F0311040D\
0917580D061106099E1106175813061106036F1000000A32BF0817580C08026F1000000A329F06068E691759942A000042534A4201000100000000000C00000076342E302E33303331390000000005006C0000009C020000237E0000080300001003000023537472696E6773000000001806000004000000235553001C060000\
1000000023475549440000002C0600005C04000023426C6F6200000000000000020000015715A2010900000000FA013300160000010000001500000003000000020000000500000006000000120000001000000003000000010000000100000001000000010000000200000000001A0201000000000006004001C9020600AD01\
C90206005F0097020F00E90200000600870064020600230164020600EF00640206009401640206006001640206007901640206009E00640206007300AA0206005100AA020600D20064020600B900DD01060008032B020A000E0176020A00390076020A00330076020600F7012B02060008002B02000000001500000000000100\
0100810110004C023F024100010001000100100058023F024100010002002100CB014F00210002035200502000000000960048005600010013210000000086189102100003003821000000008608090227000400482100000000860032025C0004000822000000009600480056000500000001000100000002000E0000000100\
D70100000100D701000001000100000002000E00090091020100110091020600190091020A00290091021000310091021000390091021000410091021000490091021000510091021000590091021000610091021500690091021000710091021000790091021000890091020600A100FE012700A100F8022B00810091020600\
20007B0089012E000B0065002E0013006E002E001B008D002E00230096002E002B00A8002E003300CE002E003B00CE002E00430096002E004B00D4002E005300CE002E005B00CE002E006300EC002E006B0016012E0073002301A0007B00F8021A0030003A000300010000000D02610002000300030004800000010000000000\
05000000000000003F02000004000000000000000000000046002A000000000004000000000000000000000046001E0000000000000000000076616C75653100496E7433320076616C756532003C4D6F64756C653E0053797374656D2E44617461006D73636F726C69620053797374656D446174614163636573734B696E6400\
44697374616E636500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B\
41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174\
696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275\
74650073746F72656456616C75650076616C75650053797374656D2E52756E74696D652E56657273696F6E696E6700537472696E67006765745F4C656E677468006765745F53746F7265644C656E6774680046617374656E73687465696E2E646C6C0053797374656D0044697374616E636546726F6D0046617374656E736874\
65696E004175746F436F6D706C6574654C6576656E73687465696E0053797374656D2E5265666C656374696F6E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F705365727669636573\
0053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F646573006765745F436861727300636F737473004F626A656374000000000000519344FEBFD39543BB4150218E52A6E100042001010803200001052001011111042001010E04200101020C07091D080808080808\
030808032000080420010308090707080808080308080B07081D080808080803080808B77A5C561934E08902060E03061D08050002080E0E042001080E032800080801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001101000C46617374656E73687465\
696E0000250100205468652066617374657374204C6576656E73687465696E206F6E204E75476574000005010000000017010012436F7079726967687420C2A920203230313700002901002437323439353730312D343331332D343538662D396131342D30393561366130333031343000000C010007312E302E302E35000065\
0100292E4E45544672616D65776F726B2C56657273696F6E3D76342E302C50726F66696C653D436C69656E740100540E144672616D65776F726B446973706C61794E616D651F2E4E4554204672616D65776F726B203420436C69656E742050726F66696C65816D01000500540E044E616D651F4175746F436F6D706C6574654C\
6576656E73687465696E44697374616E63655455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6237\
37613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C20\
5075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501816101000500540E044E616D65134C6576656E73687465696E44697374616E63655455794D6963726F736F66\
742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737300000000\
54557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6237376135633536313933346530\
38391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650100000000000290825B00000000020000001C010000542D0000540F00005253445367B8F4DCE6412341943C19418714AA4E01000000463A5C6465765C46617374207374756666735C46\
617374656E73687465696E5C7372635C46617374656E73687465696E4672616D65776F726B5C6F626A5C52656C656173655C46617374656E73687465696E2E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000982E00000000000000000000B22E0000\
002000000000000000000000000000000000000000000000A42E0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C03000000000000000000007C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000\
BD04EFFE00000100000001000500000000000100050000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC02000001005300\
7400720069006E006700460069006C00650049006E0066006F000000B802000001003000300030003000300034006200300000005A002100010043006F006D006D0065006E007400730000005400680065002000660061007300740065007300740020004C006500760065006E00730068007400650069006E0020006F006E00\
20004E0075004700650074000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000042000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000460061007300740065006E00730068007400650069006E00000000003000080001004600\
69006C006500560065007200730069006F006E000000000031002E0030002E0030002E003500000042001100010049006E007400650072006E0061006C004E0061006D0065000000460061007300740065006E00730068007400650069006E002E0064006C006C00000000004800120001004C006500670061006C0043006F00\
7000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100370000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000004A00110001004F0072006900670069006E0061006C00460069006C0065006E00\
61006D0065000000460061007300740065006E00730068007400650069006E002E0064006C006C00000000003A000D000100500072006F0064007500630074004E0061006D00650000000000460061007300740065006E00730068007400650069006E0000000000340008000100500072006F00640075006300740056006500\
7200730069006F006E00000031002E0030002E0030002E003500000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00350000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
002000000C000000C43E00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.ufn_fast_levenshtein( @value1 NVARCHAR(MAX), @value2 NVARCHAR(MAX))
RETURNS INT
AS
EXTERNAL NAME FastenshteinAssembly.[Fastenshtein.Levenshtein].Distance;
GO

You should now be able to execute your external function on the Managed Instance.

-- Example, simple comparison.
--
DECLARE @retVal AS INTEGER;
SELECT @retVal = XtrlUtils.ufn_fast_levenshtein('Test', 'test');
SELECT @retVal;
GO

-- Example, fuzzy match names between two tables
--
SELECT SP.SalesPerson,
       ADM.MemberName,
       ADM.UserPrinicpalName
FROM   dbo.SalesPerson                   AS SP
       LEFT OUTER JOIN dbo.AzureADMember AS ADM ON dbo.ufn_fast_levenshtein(ADM.MemberName, SP.SalesPerson) < 3;
GO`
Follow me