The nvarchar(max) Limitation with sp_OAMethod / sp_OAGetProperty

The Problem

SQL Server's OLE Automation procedures — sp_OAMethod and sp_OAGetProperty — are used to call methods and read properties on COM/ActiveX objects (such as the Chilkat components) from within T-SQL. When a called method returns a string, that return value is written into a local T-SQL variable via an OUT parameter.

Even though nvarchar(max) is a valid, legal T-SQL data type, sp_OAMethod and sp_OAGetProperty cannot fill an nvarchar(max) output variable correctly. Declaring the output parameter as nvarchar(max) does not raise an error, but any string longer than roughly 4,000 characters is silently truncated. There is no warning at execution time — the procedure appears to succeed, and the truncation is only discovered when the returned data is inspected.

-- This looks correct, but will truncate any HTML longer than ~4000 characters:
DECLARE @html nvarchar(max)
EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://www.example.com/'

Why This Matters

Any method that can plausibly return more than 4,000 characters is affected: HTTP responses, file contents read as text, generated XML/JSON/EML documents, base64 or hex-encoded strings, and similar. Using nvarchar(max) for the output parameter in these cases gives no compile-time or run-time indication that data has been lost.

The Workaround: KeepStringResult and LastStringResult

Chilkat's Chilkat.Global object provides a way to bypass the sp_OAMethod output-parameter limitation entirely. When KeepStringResult is enabled, every Chilkat object saves the full string returned by the last method call in a LastStringResult property. Because LastStringResult is itself read through sp_OAGetProperty, its value can be inserted directly into a table variable with an nvarchar(max) column — a path that does not truncate.

Step 1: Enable KeepStringResult once per session

DECLARE @hr int
DECLARE @global int
EXEC @hr = sp_OACreate 'Chilkat.Global', @global OUT
IF @hr <> 0
BEGIN
    PRINT 'Failed to create Global ActiveX component'
    RETURN
END

EXEC sp_OASetProperty @global, 'KeepStringResult', 1
EXEC @hr = sp_OADestroy @global

This is typically done once, at the start of a setup procedure that also handles unlocking, so every subsequent Chilkat call in the session benefits from it.

Step 2: Call the method, but ignore its direct output parameter

The output parameter is still required by sp_OAMethod's syntax, but it should be declared as a small, fixed-size nvarchar (e.g., nvarchar(4000)) and its value discarded. The real result will be fetched separately.

DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
    PRINT 'Failed to create ActiveX object'
    RETURN
END

DECLARE @dummy nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @dummy OUT, 'https://www.example.com/'

Step 3: Check for success before trusting the result

DECLARE @success int
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @success OUT
IF @success <> 1
BEGIN
    DECLARE @errTable TABLE (lastErrText ntext)
    INSERT INTO @errTable EXEC sp_OAGetProperty @http, 'LastErrorText'
    SELECT * FROM @errTable

    EXEC @hr = sp_OADestroy @http
    RETURN
END

LastErrorText can itself be long, so it is retrieved into a table variable rather than a scalar variable, for the same reason as the main result.

Step 4: Retrieve the full string from LastStringResult via a table variable

DECLARE @resultTable TABLE (res ntext)
INSERT INTO @resultTable EXEC sp_OAGetProperty @http, 'LastStringResult'
SELECT @html = res FROM @resultTable

PRINT @html

EXEC @hr = sp_OADestroy @http

Putting It Together

CREATE PROC ChilkatSetup
AS
BEGIN
    DECLARE @hr int
    DECLARE @glob int
    EXEC @hr = sp_OACreate 'Chilkat.Global', @glob OUT

    DECLARE @success int
    EXEC sp_OAMethod @glob, 'UnlockBundle', @success OUT, 'Your unlock code'
    EXEC sp_OASetProperty @glob, 'KeepStringResult', 1
END
GO

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int

    EXEC ChilkatSetup

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX object'
        RETURN
    END

    DECLARE @dummy nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @dummy OUT, 'https://www.example.com/'

    DECLARE @success int
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @success OUT
    IF @success <> 1
    BEGIN
        DECLARE @errTable TABLE (lastErrText ntext)
        INSERT INTO @errTable EXEC sp_OAGetProperty @http, 'LastErrorText'
        SELECT * FROM @errTable

        EXEC @hr = sp_OADestroy @http
        RETURN
    END

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

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

    PRINT @html

    EXEC @hr = sp_OADestroy @http
END
GO

Summary of Rules

1. Never declare an sp_OAMethod / sp_OAGetProperty output parameter as nvarchar(max) and expect the full string — it will silently truncate at roughly 4,000 characters.
2. Use a small, fixed-size nvarchar(4000) (or smaller) as the required OUT parameter, and treat its value as disposable.
3. Enable Chilkat.Global's KeepStringResult property once per session so every subsequent method call preserves its full return value in LastStringResult.
4. Retrieve LastStringResult (and LastErrorText, which can also be long) through INSERT ... EXEC into a table variable with an nvarchar(max) or ntext column, then SELECT it into a scalar variable if needed.
5. Always check LastMethodSuccess before trusting LastStringResult.