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().

Batho

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' – Subway

I'd say the correct terminology would be a hex string – Merriott

Answer 1

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;

Greasy

Thanks - the hex info led me to this code: blog.waldenl.com/2009/12/… – Batho

that's basically the same loop yes :) – Greasy

Answer 2

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.

1. 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
   

2. Browse to your assembly in Object Explorer.

3. Script its creation.

And SSMS gives you the varbinary.

Caducity

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> – Gormley

@Gormley 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. – Mcinnis