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`
