• Create the DLL using any language of your choice. For example, in this article we will use VB 6 to create a DLL.
• Initialize the DLL from SQL Server using the sp_OACreate method call.
• Call into the various methods or properties of the DLL using sp_OAMethod, sp_OAGetProperty and sp_OASetProperty calls.
• Trap all errors using the sp_OAGetErrorInfo call.
• Destroy the automation object using sp_OADestroy.

If you look at the above steps, they are very similar to how you would work with an automation object in a development environment like Visual Basic. You add a reference to the DLL that you want to work with, call its methods and finally destroy the object. One thing to remember is: you cannot call into .NET objects directly from SQL Server using the above method. Read this article for more information. The next version of SQL Server (code named Yukon) offers this capability and more.

OK, let's see all this in action. First, let's create a simple DLL in Visual Basic 6. Here is the source for this DLL.

Public Function noParamsCall() As String
    noParamsCall = "(no Parameters passed)"
End Function

Public Function paramsCall(ByVal param As String) As String
    paramsCall = "You passed : " & Trim(param)
End Function

-- Scratch variables used in the script
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)

-- Initialize the COM component.
EXEC @retVal = sp_OACreate 'Project1.Class1', @comHandle OUTPUT
IF (@retVal <> 0)
BEGIN
	-- Trap errors if any
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT [Error Source] = @errorSource, [Description] = @errorDescription
	RETURN
END

-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'noParamsCall', @retString OUTPUT
IF (@retVal <> 0)
BEGIN
	-- Trap errors if any
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT [Error Source] = @errorSource, [Description] = @errorDescription
	RETURN
END

-- Print the value returned from the method call
SELECT @retString

-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'paramsCall', @retString OUTPUT, @param='Test Value'
IF (@retVal <> 0)
BEGIN
	-- Trap errors if any
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT [Error Source] = @errorSource, [Description] = @errorDescription
	RETURN
END

-- Print the value returned from the method call
SELECT @retString

-- Release the reference to the COM object
EXEC sp_OADestroy @comHandle

• The first call is to sp_OACreate. This procedure takes as parameter the ProgID or the ClassID of the automation object and then returns a handle to the object. This handle is stored in a variable of type INT. The procedure also returns a value that indicates whether the call was successful. The handle that is returned is used for all subsequent calls to the sp_OA* methods. You can optionally pass a context parameter that specifies the execution context in which the created object will run. You can specify the following values:
  • In-process (value of 1), which indicates that the object can access SQL Server memory and other resources. Normally DLL's are loaded in-process. Use this with care, since a badly writeen DLL can crash SQL Server.
  • Out of process (value of 4), which indicates that the object cannot access SQL Server memory or resources. Normally EXE's are loaded out of process.
  • Both in-process and out of process (value of 5), which is the default.
• If the return value is not 0, we then get the error details using the sp_OAGetErrorInfo procedure. This procedure takes as parameter the handle to the object and then returns as output the source and description of the error.
• Once the object is initialized, we can call into the various methods of the object using the sp_OAMethod procedure. This procedure takes as parameter the handle to the object, the name of the method and an optional return value. Parameters can also be passed into the method if required.
• Once again we can get the error details using the sp_OAGetErrorInfo procedure.
• Once we are done with the object, we can (and should) use the sp_OADestroy procedure to release the reference to the object. This procedure takes as parameter the handle to the COM object to destroy.

Now, when you execute this program, you get the following as the output.

-------------------------------------------------------------------------------- 
(no Parameters passed)

(1 row(s) affected)

-------------------------------------------------------------------------------- 
You passed : Test Value

(1 row(s) affected)