sp_OACreate fails under Transact-SQL (SQL Server)

Problem Description

When calling a Persits Software component from a stored procedure or user-defined function written in Transact-SQL under Microsoft SQL Server, the sp_OACreate command fails after a few dozen (or a few hundred) invocations of that stored procedure or function.

Solution

Unlike VB Script, Transact-SQL does not automatically destroy COM objects. Your T-SQL script must explicitly destroy all objects it creates with the sp_OADestroy command, otherwise your T-SQL subroutine will quickly use up all memory allocated for COM objects, and sp_OACreate will start failing every time.

The following code sample demonstrates the usage of sp_OADestroy command in a simple AspEncrypt-based script:

CREATE FUNCTION dbo.AspEncryptTest ( @someparam int ) RETURNS varchar(100)
AS
BEGIN

DECLARE @hr int
DECLARE @CM Int
DECLARE @Key int
DECLARE @Context int
DECLARE @Blob int
DECLARE @encryptedValue varchar(100)

EXEC @hr = sp_OACreate 'Persits.CryptoManager', @CM OUT
IF (@hr <> 0)
BEGIN
  RETURN 'error in CreateObject'
END

EXEC @hr = sp_OAMethod @CM, 'OpenContext', @Context out, '', 1
EXEC @hr = sp_OAMethod @Context, 'GenerateKeyFromPassword', @Key out, 'my secret password', 32772, 26114, 128
EXEC @hr = sp_OAMethod @Key, 'EncryptText', @Blob out, 'Some text'
EXEC @hr = sp_OAGetProperty @Blob, 'Hex',@encryptedValue out

EXEC sp_OADestroy @Blob
EXEC sp_OADestroy @Key
EXEC sp_OADestroy @Context
EXEC sp_OADestroy @CM

return @encryptedValue

END

Comments

This article applies to AspUpload (non-upload functionality), AspJpeg, AspEmail, AspEncrypt, AspUser, AspPDF, and XUpload.

For more information on using Persits components with Transact-SQL, see the following article: PS01050851.