The varbinary(max) Limitation with sp_OAMethod

The Problem

Chilkat ActiveX methods that return raw binary data — such as BinData's GetData and GetDataChunk — hand it back to COM callers as a SAFEARRAY of VT_UI1 bytes, which is to say, an array. Microsoft's own sp_OAMethod documentation is explicit about what happens next: "When the method return value is an array, if returnvalue is specified, it's set to NULL." This is not a size-dependent truncation the way the nvarchar string limitation is — it is an unconditional rule. Declaring the output variable as varbinary(max), or as a small fixed-length varbinary(n), makes no difference; a method that returns an array can never populate a scalar output variable through sp_OAMethod. In practice this often surfaces as error -2147211494 (hex 0x80040202) rather than a silent NULL, depending on the calling pattern, but the root cause is the same array-return rule.

DECLARE @bd int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

DECLARE @success int
EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, '010203040506', 'hex'

-- This raises error 0x80040202 -- SAFEARRAY(VT_UI1) cannot be returned this way:
DECLARE @binary_string varbinary(max)
EXEC sp_OAMethod @bd, 'GetData', @binary_string OUT

Why This Matters

Any method returning raw bytes directly — reading a binary BinData buffer, pulling a chunk out of it, or any similarly-shaped API on another Chilkat object — cannot be captured this way. The fix is not a different varbinary declaration; no declaration of the output parameter makes the direct byte-array return path work reliably through sp_OAMethod.

Workaround A: KeepBinaryResult and LastBinaryResult

Chilkat's BinData object exposes a LastBinaryResult property — a direct binary counterpart to the LastStringResult property used for the nvarchar limitation. It is populated only when Chilkat.Global's KeepBinaryResult property is set to 1, and it must be retrieved through a table variable via INSERT ... EXEC rather than a scalar OUT parameter, since that is the code path that isn't restricted to scalar, non-array results.

DECLARE @global int
EXEC @hr = sp_OACreate 'Chilkat.Global', @global OUT
EXEC sp_OASetProperty @global, 'KeepBinaryResult', 1
EXEC @hr = sp_OADestroy @global

-- Call GetData, but ignore its (NULL) return value -- the real bytes end up in LastBinaryResult:
DECLARE @dummy varbinary(1)
EXEC sp_OAMethod @bd, 'GetData', @dummy OUT

DECLARE @binResultTable TABLE (bin varbinary(max))
INSERT INTO @binResultTable EXEC sp_OAGetProperty @bd, 'LastBinaryResult'

DECLARE @binaryData varbinary(max)
SELECT @binaryData = bin FROM @binResultTable

This is generally the preferred workaround: it stays entirely in binary form, with no text-encoding round trip and none of the separate string-length limitation to worry about.

Workaround B: Encode as Base64, Then Convert

Alternatively, ask the method to hand back the same bytes encoded as a Base64 (or hex) string instead of a raw byte array. String return values are something sp_OAMethod can handle directly (subject to the separate nvarchar string-length limitation), and Base64 text can be converted back into varbinary(max) entirely within T-SQL using the built-in XML type's base64Binary function. This avoids depending on the KeepBinaryResult mechanism, at the cost of an extra encode/decode step.

Step 1: Get the data as Base64 instead of raw bytes

For BinData, call GetEncoded with 'base64' instead of GetData. Other Chilkat classes expose an analogous encode-to-string method for whatever binary content they hold.

DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @bd int
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, 'AppendEncoded', @success OUT, '010203040506', 'hex'

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

Step 2: Convert the Base64 string to varbinary(max) in T-SQL

DECLARE @binaryData varbinary(max)
SET @binaryData = CAST('' AS XML).value('xs:base64Binary(sql:variable("@sTmp0"))', 'VARBINARY(MAX)')

EXEC @hr = sp_OADestroy @bd

This conversion is pure T-SQL — no COM call is involved — so it is not subject to the sp_OAMethod marshaling limitation at all.

Step 3: For large binary payloads, combine with KeepStringResult

Base64 text is still a string return value, so it is subject to the same roughly-4,000-character truncation limit described for nvarchar(max) output parameters. For binary payloads whose Base64 form exceeds that size, retrieve the encoded string through Chilkat.Global's KeepStringResult / LastStringResult mechanism first, then convert.

DECLARE @global int
EXEC @hr = sp_OACreate 'Chilkat.Global', @global OUT
EXEC sp_OASetProperty @global, 'KeepStringResult', 1
EXEC @hr = sp_OADestroy @global

DECLARE @dummy nvarchar(4000)
EXEC sp_OAMethod @bd, 'GetEncoded', @dummy OUT, 'base64'

DECLARE @resultTable TABLE (res ntext)
INSERT INTO @resultTable EXEC sp_OAGetProperty @bd, 'LastStringResult'

DECLARE @base64 nvarchar(max)
SELECT @base64 = res FROM @resultTable

DECLARE @binaryData varbinary(max)
SET @binaryData = CAST('' AS XML).value('xs:base64Binary(sql:variable("@base64"))', 'VARBINARY(MAX)')

DECLARE @success int
EXEC sp_OAMethod @bd, 'AppendData', @success OUT, @binaryData, DATALENGTH(@binaryData)

Putting It Together

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @success int
    DECLARE @bd int
    DECLARE @global int

    -- Enable KeepBinaryResult once per session.
    EXEC @hr = sp_OACreate 'Chilkat.Global', @global OUT
    EXEC sp_OASetProperty @global, 'KeepBinaryResult', 1
    EXEC @hr = sp_OADestroy @global

    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load some sample bytes into the BinData object.
    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, '010203040506', 'hex'

    -- GetData's return value is an array, so this OUT parameter is always NULL -- ignore it.
    DECLARE @dummy varbinary(1)
    EXEC sp_OAMethod @bd, 'GetData', @dummy OUT

    -- Retrieve the real bytes from LastBinaryResult via a table variable.
    DECLARE @binResultTable TABLE (bin varbinary(max))
    INSERT INTO @binResultTable EXEC sp_OAGetProperty @bd, 'LastBinaryResult'

    DECLARE @binaryData varbinary(max)
    SELECT @binaryData = bin FROM @binResultTable

    -- Sending varbinary back in as an input argument works directly -- no workaround needed.
    EXEC sp_OAMethod @bd, 'AppendData', @success OUT, @binaryData, DATALENGTH(@binaryData)

    PRINT 'OK.'
    EXEC @hr = sp_OADestroy @bd
END
GO

Summary of Rules

1. Never expect sp_OAMethod to return a byte array into a varbinary output parameter, at any size — per Microsoft's own documentation, when a method's return value is an array, a specified returnvalue OUTPUT variable is set to NULL. This is unconditional, not a size-based truncation like the nvarchar limitation.
2. Prefer enabling Chilkat.Global's KeepBinaryResult property and retrieving the bytes from LastBinaryResult through a table variable (INSERT ... EXEC) — this keeps the data in true binary form with no text encoding involved.
3. Alternatively, have the ActiveX method encode its binary content as a Base64 (or hex) string, retrieve that string, and convert it to varbinary(max) in T-SQL with CAST('' AS XML).value('xs:base64Binary(sql:variable("@var"))', 'VARBINARY(MAX)').
4. If using the Base64 route and the encoded text itself can exceed roughly 4,000 characters, retrieve it via KeepStringResult / LastStringResult (see the companion article on the nvarchar limitation) before converting it.
5. The limitation only affects the return path. Passing a varbinary variable as an input argument to a method (e.g., AppendData) works normally and needs no workaround.