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()
.
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';
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.
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.
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.
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;
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With