Itay Rosenboim

Call external DLL using SQL server

Discussion created by Itay Rosenboim on Aug 8, 2018

Here is a sort list of steps i used in order to call a DLL function/s using SQL database (utilizing Boomi program command shape).

1. Add the DLL to SQL assembly using the following SQL commands 

CREATE ASSEMBLY {ASM NAME OF THE DLL} 
FROM 'C:\{DLL PATH}\{DLL NAME}.DLL' --dll path
WITH PERMISSION_SET = SAFE; 

 

2. Create an SQL function (that will be used from the Boomi program command shape or other) 

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [{FUNCTION NAME}](@param1 Int, @param2 [nvarchar](20), etc...)
RETURNS TABLE(
ReturnVal1 NVARCHAR(50),

ReturnVal2 NVARCHAR(20),

ReturnVal3 NVARCHAR(20)

...
)
--WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [{ASM NAME OF DLL}].[CLASS.FUNCTION].[FUNCTION]

GO

 

3. If not enabled already you have to enable CLR

EXEC sp_configure 'clr enabled';
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;

GO

 

4. Call the function from Boomi (program command shape or other)

select * from [dbo].[{FUNCTION NAME}](1111,'STRING',...) 

 

Outcomes