I’m assuming that the DLL is not part of the .Net framework. If my assumption is correct then the DLL needs to be added to SQL Server as an assembly prior to your assembly being installed. Once the third party DLL is an assembly within SQL Server, then SQL Server “knows” about it and it could be referenced by another external procedure. Here is an example script.
EXECUTE sp_configure 'show advanced options' , '1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE sp_configure 'clr enabled', '1';
RECONFIGURE WITH OVERRIDE;
GO
-- Beacause the assembly is accessing a third party DLL, the database needs to be set to TRUSTWORTHY
ALTER DATABASE [JUNK] SET TRUSTWORTHY ON
GO
USE [JUNK]
GO
-- Create assembly using thrid party DLL
CREATE ASSEMBLY ThirdPartySDK
FROM 'C:\Program Files\SomeProgram\SDK.dll'
WITH PERMISSION_SET = UNSAFE;
GO
-- Create external function that references the SDK.DLL installed above
CREATE FUNCTION fnGetValue
(
@Timestamp As DateTime,
@UserName As nVarchar(256) = '',
@Password As nVarchar(256) = ''
)
RETURNS [sql_variant] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyExtendedProcedures].[Functions].[fnGetValue]
GO
GRANT EXECUTE ON [dbo].[fnGetValue] TO Public
GO
-- Solomon Rutzky - I hope I'm not stealing your thunder 🙂