To create an OLE Automation object:

1. Call sp_OACreate to create the object. 2. Use the object.

• Call sp_OAGetProperty to get a property value.
• Call sp_OASetProperty to set a property to a new value.
• Call sp_OAMethod to call a method.
• Call sp_OAGetErrorInfo to get the most recent error information.
• Call sp_OADestroy to destroy the object.

The following code snippet is just examples of using the OLE automation object. It is not a ready-to-go script.

-- ... a part of a stored procedure:
DECLARE @object INT
DECLARE @hr INT
DECLARE @src VARCHAR(255), @DESC VARCHAR(255)
EXEC @hr = sp_OACreate 'Encryptor.Encrypt', @object OUT
IF @hr <> 0 GOTO ERRORHANDLER
 
DECLARE @SOURCE AS VARCHAR(20), @dest AS VARCHAR(20)
DECLARE curCC CURSOR FOR SELECT CreditCardNumber FROM Orders WHERE PaymentMethod=0
 
OPEN curCC
FETCH NEXT FROM curCC INTO @SOURCE
WHILE @@FETCH_STATUS != -1
BEGIN
    EXEC @hr = sp_OAMethod @object, 'Decrypt', @dest OUT, @SOURCE 
    IF @hr <> 0 GOTO ERRORHANDLER
        UPDATE Orders SET CCN=@dest WHERE CURRENT OF curCC
    FETCH NEXT FROM curCC INTO @SOURCE
END
DEALLOCATE curCC
 
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ERRORHANDLER
    RETURN
 
ERRORHANDLER:
BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @DESC OUT 
    SELECT hr=CONVERT(varbinary(4),@hr), SOURCE=@src, Description=@DESC
    RETURN
END