Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CLR SQL Assembly: Get the Bytestream?

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

like image 950
OMG Ponies Avatar asked May 21 '10 20:05

OMG Ponies


People also ask

How can check CLR assembly permission set in SQL Server?

To determine if CLR is enabled, execute the following commands: EXEC SP_CONFIGURE 'show advanced options', '1'; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'clr enabled';

What is SQL CLR assembly?

NET Framework common language runtime (CLR), instead of in Transact-SQL. An assembly in SQL Server is an object that references a managed application module (. dll file) that was created in the . NET Framework common language runtime. An assembly contains class metadata and managed code.

Where are CLR assemblies stored?

Deployed User CLR assemblies are stored in the database you deploy them to, not on the file system. the column called content contains binary data is the assembly.

What is CLR function in SQL Server?

CLR functions can be used to access external resources such as files, network resources, Web Services, other databases (including remote instances of SQL Server). This can be achieved by using various classes in the . NET Framework, such as System.IO , System. WebServices , System. Sql , and so on.


2 Answers

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;
like image 150
Sander Rijken Avatar answered Sep 21 '22 07:09

Sander Rijken


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.

    Browsing to assembly

  3. Script its creation.

    Scripting the assembly

And SSMS gives you the varbinary.

like image 28
Frédéric Avatar answered Sep 20 '22 07:09

Frédéric