Calling DLL from Stored Procedure

Question

Post reply

Calling DLL from Stored Procedure

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
April 5, 2005 at 2:46 pm

#163099

Appreciated if someone will help me.

I have created DLL in vb which refreshes the Excel Pivot table with uptodate data from database. It's working fine when I call that DLL from VB but when I call that DLL through Stored Procedure ,it's not working.

I call DLL like this

exec SP_DLLCALL 'prjXL_eob', 'clsXL', 'Main', ''

PrjXL_eob is the dll name

clsXL is the class module

Main is the function

I also copy my dll in the windows/system32 folder.

What else should I do ? .

Following is the Stored Procedure

CREATE    procedure SP_DLLCall

@DllName Varchar(100),

@ClassName Varchar(100),

@MethodName Varchar(100),

@ArgumentString Varchar(200)

as

DECLARE @oPKG int

DECLARE @hr int

DECLARE @DLLANDCLASS VARCHAR(200)

DECLARE @METHODANDARG VARCHAR(300)

SET @DLLANDCLASS = @DLLNAME + '.' + @CLASSNAME

SET @METHODANDARG = ltrim(rtrim(@METHODNAME)) +

'( ' +

ltrim(rtrim(@ArgumentString ))

+ ')'

--Creating the DTS Package Object:

EXEC @hr = sp_OACreate @DLLANDCLASS, @oPKG OUT

IF @hr <> 0

BEGIN

    PRINT '*** Create Package object failed'

    EXEC sp_displayoaerrorinfo @oPKG, @hr

    RETURN

END

--Loading the Package: -- DTSSQLServerStorageFlags :

--- DTSSQLStgFlag_Default = 0

--- DTSSQLStgFlag_UseTrustedConnection = 256

EXEC @hr = sp_OAMethod @oPKG,

@METHODANDARG

IF @hr <> 0

BEGIN

    PRINT '*** Load Package failed' + @methodandarg

    EXEC sp_displayoaerrorinfo @oPKG, @hr

    RETURN

END

--Executing the Package:

EXEC @hr = sp_OAMethod @oPKG, 'Execute'

IF @hr <> 0

BEGIN

    PRINT '*** Execute failed' + @METHODANDARG

    EXEC sp_displayoaerrorinfo @oPKG , @hr

    RETURN

END

--Cleaning up:

EXEC @hr = sp_OADestroy @oPKG

IF @hr <> 0

BEGIN

    PRINT '*** Destroy Package failed'

    EXEC sp_displayoaerrorinfo @oPKG, @hr

    RETURN

END

GO