• Hi Eric. Good topic. I definitely like the concept, but need to mention a few notes regarding SQLCLR best practices:

    • !! Critical Issue !!: You open a FileStream but you don't have any error handling or even a "using" block. This is very dangerous as external resources (i.e. file handles, network handles, etc) can get orphaned if not properly disposed and can be held open at least until the App Domain is unloaded. So, the File.Open() through the File.Read() needs to either be in a try / catch / finally (where you call fs.Dispose() in the finally block), or at least in a "using" block (which is really just short-hand for the compiler doing the try / catch / finally for you). And then you don't need to call fs.Close().

      Until the code in the article is updated to trap errors to ensure proper disposal, I would have to recommend against anyone using this in a Production capacity.

    • Why are you chopping off the last byte of the file?
    • Why are you both setting the database to TRUSTWORTHY ON and granting yourself EXTERNAL ACCESS ASSEMBLY? Neither of those things are necessary. And in fact, setting TRUSTWORTHY ON is usually undesirable. If you sign the Assembly and give a password to the key file (leaving you with a .pfx file), then you can just create an Asymmetric Key in the [master] database from your Assembly DLL, then you can create a Login based on that Asymmetric Key, and then you grant that Login EXTERNAL ACCESS ASSEMBLY. Then you can load the Assembly as EXTERNAL_ACCESS without needing to set the database to TRUSTWORTHY ON.
    • Why would you copy the DLL to “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL”? I don't see any benefit to that as the DLL isn't needed after it is imported into SQL Server.
    • I'm pretty sure that you don't need the "WITH EXECUTE AS 'SBHR1\enotheisen'" option since the code should be accessing the external resource as the Log On Account for the MSSQLSERVER (or whatever) service unless you handle the impersonation within the CLR code. Of course, if you are testing on SQL Server Express LocalDB, that code will access external resources as you since it runs in user space and not as a background system process.
    • I think you are missing some options/flags in your csc command line. I let Visual Studio handle all of that for me so I don't even know what they all are, but at the very least there should be one for optimizing the code.

    I hope all of this helps. Take care, Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR