Returning Binary Data from ActiveX to SQL Server varbinary(max)

October 11, 2024

Returning Binary Data from ActiveX to SQL Server varbinary(max)

The error -2147211494 (hex 0x80040202) in SQL Server when using “sp_OAMethod” typically indicates a type mismatch or invalid data type issue, particularly when interacting with COM objects that return binary data.

The Chilkat ActiveX returns a SAFEARRAY of VT_UI1, which causes the problem explained below. The solution is to instead look for the Chilkat method that returns the binary data as base64. For example, if we have a BinData, you can instead get the base64 data by calling GetEncoded as shown here:

See further below for information about how to convert a base64 string to varbinary(max)
Also beware of limitations on lengths of strings returned from sp_OAMethod. See SQL sp_OAMethod String Length Return Value Limitations
The following code snippet is for strings that are not too large. See SQL sp_OAMethod String Length Return Value Limitations for the workaround to return larger strings.
In summary, to get a large amount of binary data into a varbinary(max), you’ll need to return the data as base64 using the workaround explained in the linked post above, then convert the base64 to binary as described below. In general SQL Server is a massive pain in the ass when dealing with large strings or binary data.

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/jpg/starfish.jpg'
    IF @success = 0
      BEGIN
        PRINT 'Failed to load file.'
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'base64'
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @bd
END

In the context of “sp_OAMethod” and binary data, this usually means that SQL Server is expecting one data type, but the COM method is returning a different one or the data returned is not being handled correctly by SQL Server.

Common Causes and Solutions for 0x80040202:

Here’s a breakdown of the likely causes and how to resolve them:

1. Incorrect Data Type Handling:

0x80040202 is often associated with mismatched data types when using OLE Automation Procedures in SQL Server.
If the COM method you are calling returns a binary array (like a “SAFEARRAY” of “VT_UI1”), SQL Server needs to handle it as a “VARBINARY”.
This error can occur if the data returned by the ActiveX method is not directly convertible to the type expected by “sp_OAMethod”.Solution:
Ensure that the ActiveX method is designed to return a “byte” array (“VT_ARRAY | VT_UI1”) if you want to store it as “VARBINARY” in SQL Server.
If the ActiveX method returns a different VARIANT type, such as “VT_BSTR” (string), you may need to modify the method or write a wrapper to convert it to the correct format before calling it from SQL Server.

2. Handling SAFEARRAY Properly:

When dealing with binary data in ActiveX methods, the data is often returned as a “SAFEARRAY” of bytes (“VT_ARRAY | VT_UI1”).
SQL Server has limited support for handling SAFEARRAY directly through “sp_OA*” procedures. If the COM object is returning data as a SAFEARRAY of bytes, SQL Server might not be able to process it correctly.Solution:
You may need to modify your ActiveX component to convert the binary data into a hex-encoded string or Base64 string before returning it to SQL Server.
Then, you can convert the hex-encoded or Base64 string back to “VARBINARY” inside SQL Server using functions like “CAST” or “CONVERT”.Example:

If your ActiveX component returns a Base64-encoded string:

DECLARE @base64String NVARCHAR(MAX);
DECLARE @binaryData VARBINARY(MAX);
EXEC sp_OAMethod @object, 'GetBase64Data', @base64String OUTPUT;
SET @binaryData = CAST('' AS XML).value('xs:base64Binary(sql:variable("@base64String"))', 'VARBINARY(MAX)');
INSERT INTO YourTable (YourBinaryColumn)
VALUES (@binaryData);

3. COM Component Error:

Sometimes, 0x80040202 can occur due to an issue with the COM component itself.
It may be failing internally while trying to return the data, especially if it is not able to correctly serialize the binary data.Solution:
Test the COM method independently outside of SQL Server, such as using a VBScript or PowerShell to verify that it correctly returns the expected binary data.
Ensure that the COM object is registered properly and that it behaves as expected when returning binary data.

4. Missing or Improper Data Initialization:

This error can also occur if the output parameter in “sp_OAMethod” is not properly initialized to receive the data from the COM method.
Make sure that the output parameter is defined correctly in your “sp_OAMethod” call.Solution:
Ensure that you have declared the output parameter as “VARBINARY” if it is intended to receive binary data.
Confirm that the COM method expects to return a “byte” array or another compatible type.

Example of Correctly Handling Binary Data:

If your ActiveX method is supposed to return binary data, you might use the following approach:

DECLARE @object INT;
DECLARE @binaryData VARBINARY(MAX);
DECLARE @hr INT;

-- Create the COM object (replace 'YourActiveX.ProgID' with actual ProgID)
EXEC @hr = sp_OACreate 'YourActiveX.ProgID', @object OUT;
IF @hr <> 0
BEGIN
    PRINT 'Error creating ActiveX object';
    RETURN;
END;

-- Call the method that returns binary data as Base64 (modify as needed)
DECLARE @base64String NVARCHAR(MAX);
EXEC @hr = sp_OAMethod @object, 'GetBase64EncodedData', @base64String OUT;
IF @hr <> 0
BEGIN
    PRINT 'Error calling ActiveX method';
    EXEC sp_OADestroy @object;
    RETURN;
END;

-- Convert the Base64 string to VARBINARY
SET @binaryData = CAST('' AS XML).value('xs:base64Binary(sql:variable("@base64String"))', 'VARBINARY(MAX)');

-- Store the binary data in your table
INSERT INTO YourTable (YourVarbinaryColumn)
VALUES (@binaryData);

-- Clean up
EXEC sp_OADestroy @object;

Summary:

Error 0x80040202 usually indicates a type mismatch when handling the data returned from a COM method.
Verify that the ActiveX method returns a data type that is compatible with what SQL Server can handle (“VT_ARRAY” of “VT_UI1” for binary).
Consider converting binary data into a Base64-encoded string or hex string before returning it to SQL Server, then decoding it back to “VARBINARY”.
Test the COM method independently and ensure the output is what SQL Server expects to handle.

admin