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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

Did you register the dll on the server?

What is the error message?

Andy.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Thanks for your reply.

How to register dll on the server

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 3

I did register the DLL but when I run the SP it's taking forever.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

when I try to kill the SPID secound time , I am getting following error like ;

SPID 59: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

And also when I try to delete the dll from c:\winnt\systems32 folder, I cannot because of sharing voilation.