Call method from .Net assembly in T-SQL

The topic is simple and has been blogged on many times, so, just adding my share of how I did it on my machine putting together multiple sources. One note is that the method in the assembly is calling a web service, so it must have external access. I'm starting of course with the basic MSDN page on how to declare an assembly in SQL and then create the stored procedure for calling a method this: https://msdn.microsoft.com/en-us/library/ms189524.aspx and https://msdn.microsoft.com/en-us/library/ms187926.aspx.

Following from here, are a few items I had to research.

First of all issuing the simple

use MicrosoftDynamicsAX

create assembly [AssemblyClassWithWSMethod]

from 'c:\Users\icordobin\ClassWithWSMethod.dll'

with permission_set = external_access

will raise an error that some settings / rights are not enabled on the server. You will have to run


sp_configure 'show advanced options', 1;

go

reconfigure;

go

sp_configure 'clr enabled', 1;

go

reconfigure;

go

alter database MicrosoftDynamicsAX set trustworthy on

Now, you are able to run again the create assembly and then run the

create procedure [CallWSMethod]

    @parm1                nvarchar(max),

    @parm2                nvarchar(max),

    @parm3                bit,

    @parmOut              nvarchar(max) output

as

    external name AssemblyClassWithWSMethod.[AssemblyName.ClassName].MethodName

Now, you are able to call your method like

declare       @return_value int,

              @parm1 nvarchar(max),

              @parm2 nvarchar(max),

              @parm3 bit,

              @parmOut nvarchar(max)

exec @return_value = [dbo].[CallWSMethod]

                       @parm1 = '',

                       @parm2 = '',

                       @parm3 = true,

                       @parmOut = @parmOut output

select @parmOut as N'@parmOut'

select 'Return Value' = @return_value

GO