1

I'm using since SQL Server 2012 that support CLR Assembly trigger (coded with C#).

But I'm now finding possibility to move Azure SQL but it says CLR Assembly is not supported.

Is it sure that Azure SQL doesn't support CLR Assembly and no way to do it?

Msg 10341, Level 16, State 100, Line 5
Assembly 'MyProject.SQLCLR' cannot be loaded because Azure SQL Database does not support user-defined assemblies. Contact Azure Technical Support if you have questions.

I also found this article but unclear for me.

Does or does not SQL Azure support CLR assemblies?

sugy21
  • 119
  • 9
  • Looks like you can compile your dll locally then run [CREATE ASSEMBLY FROM BINARY](https://learn.microsoft.com/en-gb/sql/t-sql/statements/create-assembly-transact-sql?view=sql-server-ver15) sql with the binary bytes of the dll loaded into a VARBINARY variable (you cannot access the file system on azure sql db). Show the sql you used to get your error message – Caius Jard Nov 04 '19 at 07:36
  • The information in the link is pretty clear - SQL CLR is not supported. – gotqn Nov 04 '19 at 08:43
  • @gotqn what you you mean by "the link" and "the information"? – Caius Jard Nov 04 '19 at 08:52
  • @CaiusJard Check the link https://stackoverflow.com/questions/37342550/does-or-does-not-sql-azure-support-clr-assemblies - check the answer. – gotqn Nov 04 '19 at 08:53
  • @CaiusJard Yes, there is no SQL CLR for `SQL Database`. – gotqn Nov 04 '19 at 09:51
  • There is for Managed Instance. Things in technology move on; it doesn't always pay to heed an ancient answer – Caius Jard Nov 04 '19 at 12:07

1 Answers1

2

The fine manual indicates that an sql like:

CREATE ASSEMBLY SomeName
FROM 0x...
WITH PERMISSION_SET = SAFE;

will work; all you have to do is turn your dll into a string of hexadecimal number pairs representing the bytes and put them in where the ... are. The example is at the very bottom of the MSDN doc. It would be important to note though that that doc explicitly states its advice is applicable to Azure SQL Managed Instance which is a relatively recent provision; ensure that is what you have deployed. See this blog for a more in depth discussion

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Hi Caius, Thanks for the answer. I also created assembly like that. But it doens't work on Azure SQL. – sugy21 Nov 04 '19 at 08:44
  • 1
    Is your Azure SQL a Managed Instance or is it an older deployment? From what I read this only works on manged instance – Caius Jard Nov 04 '19 at 08:51
  • 1
    Dear Caius, I didn't know there were "Sql database" and "Sql managed Instance" both. Really thanks and sorry for my short confirmation. Now I'm trying to do it with managed instance. I will let you know the result – sugy21 Nov 04 '19 at 09:09
  • 1
    Dear Caius, I suceeded with managed instance. CLR works fine! Thanks – sugy21 Nov 05 '19 at 08:42