CLR SQL Assembly: Get the Bytestream?

Question

I have a SQL CLR dll I want to deploy, but have found you can embed the byte stream/varbinary_literal/ varbinary_expression/assembly bits into a text file to get around the messy hassle of packaging a DLL and making sure it's accessible for the CREATE ASSEMBLY command.

But what I have yet to find is how to get that byte stream/varbinary_literal/ varbinary_expression/assembly bits value. I haven't found any consistent terminology, and what I keep finding in using Load().

SELECT @assemblyBinary = AF.content FROM sys.assemblies AS A INNER JOIN sys.assembly_files AS AF ON A.assembly_id = AF.assembly_id WHERE A.name = 'Assembly_CLRFunction' — Mohammad Afrashteh, Apr 13 '18 at 07:14

score 24 · Accepted Answer · answered May 21 '10 at 22:29

It's just a hex representation of the dll. This bit should do the trick:

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

        StringBuilder builder = new StringBuilder();
        builder.Append("0x");

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

        return builder.ToString();
    }

You should use the resulting string like so:

string hexString = GetHexString(assemblyPath);
string sql = "CREATE ASSEMBLY [" + assemblyName + "] FROM " + hexString + 
             " WITH PERMISSION_SET = " + somePermissionSet;

Thanks - the hex info led me to this code: http://blog.waldenl.com/2009/12/command-line-hex-representation-of-file.html — OMG Ponies, May 22 '10 at 22:16

Frédéric · Answer 2 · 2018-10-30T15:53:36.793

9

Found here, the varbinary can be generated without custom code for generating it, only by using SQL Server Management Studio (SSMS) and a local SQL Server instance.

create or alter your assembly in your database using its local path on your local SQL Server.

use yourBase
go
create assembly YourAssemblySqlName from N'YourLocalPath\YourAssemblyFile.dll'
go

Browse to your assembly in Object Explorer.
Script its creation.

And SSMS gives you the varbinary.

edited Oct 30 '18 at 15:53

answered Apr 01 '16 at 18:03

Frédéric

9,364
3
62
112

5

You can also get it like this: `SELECT af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = <@assemblyName>` – rahvin_t Nov 28 '16 at 16:21
1

@rahvin_t Nice query, but in the 'where' clause, you probably want to target/change to the 'af' alias rather than "a" alias - as in 'af.name = ...', in case of a (debug) 'PDB' assembly has been loaded too. – DennisVM-D2i Jun 23 '22 at 11:32

CLR SQL Assembly: Get the Bytestream?

2 Answers2

Linked