SQL 2000 not releasing COM+ component

I'm running triggers that instantiate a .NET COM+ object via sp_OACreate, call a method via sp_OAMethod, then release it via sp_OADestroy. Everything works great.

The problem is with deployment. If I want to deploy a new version of the .NET COM+ object - even if the interface is unchanged, I have to restart SQL Server to release the handle on it. sp_OADestory should do that for me, but for some reason, SQL Server still holds onto it. This happens even if I shutdown the object in Component Services.

Any ideas of why this is happening and how to get around it? Thanks.
LVL 3
dplambertAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Your code seems fine.

Now, 'CTII.XDOCK.X2PUtil.TriggerHandler' is in a dll, i assume CTII.dll.
You might use:
DBCC CTII (free)

CHeers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Check out this:

DBCC dllname (FREE)
Unloads the specified extended stored procedure dynamic-link library (DLL) from memory.

Examples
This example assumes an extended procedure xp_sample is implemented as Xp_sample.dll and has been executed. It uses the DBCC dllname (FREE) statement to unload the Xp_sample.dll file associated with the xp_sample extended procedure.

DBCC xp_sample (FREE)

CHeers
0
 
dplambertAuthor Commented:
Well, it's not an extended stored procedure. Here's the code:

CREATE TRIGGER dbo.TrailerAfterInsertUpdate
     ON Trailer AFTER INSERT, UPDATE
AS

SET NOCOUNT ON

/* can't affect more than 1 record at a time */
IF ( SELECT COUNT(*) FROM inserted ) > 1 BEGIN
     RAISERROR('multiple INSERTs/UPDATEs not supported in TrailerAfterInsertUpdate trigger', 16, 1) WITH LOG
     RETURN
END

/* get primary key data */
DECLARE
     @TerminalId    CHAR(4),
     @TrailerNumber CHAR(12)

SELECT
     @TerminalId    = TerminalId,
     @TrailerNumber = TrailerNumber
FROM
     inserted

/* call middle tier component */
DECLARE
     @ihandle INT,
     @ireturn INT,
     @breturn BIT,
     @message VARCHAR(400)

EXEC @ireturn = sp_OACreate 'CTII.XDOCK.X2PUtil.TriggerHandler', @ihandle OUT
IF @ireturn = 0 BEGIN
     EXEC @ireturn = sp_OAMethod
          @ihandle,
          'CloseTrailer',
          @breturn OUT,
          @TerminalId,
          @TrailerNumber
     IF @ireturn <> 0 SET @message = 'unable to OAMethod'

     EXEC @ireturn = sp_OADestroy @ihandle
     IF @ireturn <> 0 AND @message IS NULL SET @message = 'unable to OADestory'
END
ELSE
     SET @message = 'unable to OACreate'

IF @message IS NOT NULL BEGIN
     SET @message = @message + ' in TrailerAfterInsertUpdate trigger, pk{'
                    + 'TerminalId=' + @TerminalId + ','
                    + 'TrailerNumber=' + @TrailerNumber + '}'
     RAISERROR(@message, 16, 1) WITH LOG
END

IF @breturn = 1
     UPDATE
          Trailer
     SET
          ToHostDateTime = CURRENT_TIMESTAMP
     WHERE
          TerminalId    = @TerminalId AND
          TrailerNumber = @TrailerNumber
0
 
dplambertAuthor Commented:
The command that worked was: DBCC X2PUtil (FREE), because that was the actual DLL name.

Thanks a lot for the help!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.