PS051122152: sp_OACreate fails under Transact-SQL (SQL Server)

Article PS051122152

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.

Created: 11/22/2005 5:33:54 PM
Last Modified: 11/22/2005 5:33:54 PM
Copyright © Persits Software, Inc. 1998 - 2023
For technical support, write to support@persits.com.