To create an OLE Automation object:
1. Call sp_OACreate to create the object. 2. Use 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