0

I am facing an issue during CREATE ASSEMBLY command in SQL Server, error message is given below:

Msg 6544, Level 16, State 1, Line 19
CREATE ASSEMBLY for assembly 'dotnetpiper.CLR' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub

I'm using this query:

CREATE ASSEMBLY dotnetpiper
from 'D:\My\dotnetpiper.CLR.dll'
WITH PERMISSION_SET = UNSAFE

Please advise me of any mistakes I'm making. I've not found anything helpful so far on this.

Sachin Kalia
  • 1,027
  • 14
  • 24

1 Answers1

2

Mixed-mode Assemblies are not allowed in SQLCLR Assemblies; only pure MSIL Assemblies are allowed.

I have this particular issue more fully documented in the following Stack Overflow answer:

SQL Server: "CREATE ASSEMBLY for assembly 'Test' failed because assembly 'Test' is malformed or not a pure .NET assembly."

The following Stack Overflow question covers the same issue, and also shows that the same source DLL -- microsoft.visualbasic.activities.compiler -- is the cause:

Register CLR function (WCF based) in SQL Server 2012

You will have to either find a way to get dotnetpiper work without referencing microsoft.visualbasic.activities.compiler, or you will have to find a way around using dotnetpiper.

One possibility is to get the basic functionality you need from dotnetpiper exposed via a Console App (which is not nearly as restricted as the SQLCLR environment) and then execute the Console App via everyone's favorite xp_cmdshell.

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Thanks srutzky for your quick response,While we've tried the above link and didn't get success. Apart from this we are creating .dll using clsss library project and referencing that into SQL using Create Assembly with unsafe also tried numerous ways but stills struggling. – Sachin Kalia Aug 23 '16 at 03:34
  • @SachinKalia I am not sure what you mean by "_we've tried the above link_". There is nothing to try outside of what I mentioned here about trying to find a way around **microsoft.visualbasic.activities.compiler** OR creating a Console App. But there is no way for you to load your Assembly if it references **microsoft.visualbasic.activities.compiler** since _that_ library cannot be loaded into SQL Server as it is a mixed-mode DLL. – Solomon Rutzky Aug 23 '16 at 03:49
  • Thanks Sir , We've tried the above link means that we've read and implemented the advise given there. We've class library which we are trying to add as an assembly. Can we make our dll as pure mode dll so that we may load it. – Sachin Kalia Aug 23 '16 at 05:32
  • 1
    @SachinKalia Your DLL, as long as it is just C# and no VC++, is not the problem. It is the **microsoft.visualbasic.activities.compiler** DLL that is the problem. That DLL, **microsoft.visualbasic.activities.compiler**, is not available in SQLCLR, so the `CREATE ASSEMBLY` command is trying to import it because it is referenced by your **dotnetpiper.CLR** DLL. But the **microsoft.visualbasic.activities.compiler** DLL cannot be loaded, so that means that your **dotnetpiper.CLR** DLL also cannot be loaded. – Solomon Rutzky Aug 23 '16 at 06:02
  • Thank you so much for all your assistance srutzky. I may need you in future than definitely will chase you :) – Sachin Kalia Aug 24 '16 at 12:20