SQL Server Methods Returning Large Strings

When using Chilkat ActiveX components within SQL Server stored procedures, you might encounter limitations when handling long strings returned by certain methods. This is due to SQL Server's constraints on the maximum size of local string variables, which can truncate or fail to capture lengthy outputs.

Problem Overview

SQL Server's sp_OAMethod and sp_OAGetProperty procedures have limitations on the length of strings they can return to local variables. Typically, this limit is around 4,000 characters. Therefore, when a Chilkat method returns a string exceeding this length, assigning it directly to a local variable may result in data truncation or errors.

Solution: Utilize KeepStringResult and LastStringResult

Chilkat provides a mechanism to handle long strings through the KeepStringResult property and the LastStringResult property. By setting KeepStringResult to true, Chilkat stores the result of the last method call in LastStringResult, allowing you to retrieve the full string even if it's too long for a standard variable.

Step 1: Enable KeepStringResult

First, create an instance of the Chilkat.Global object and set its KeepStringResult property to 1 (true). This instructs Chilkat to store the result of subsequent method calls.

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

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

Step 2: Invoke the Desired Method

Next, create an instance of the Chilkat object you intend to use (e.g., Chilkat.Mht) and call the method that returns a long string. Instead of capturing the return value directly into a local variable, you'll retrieve it from LastStringResult.

DECLARE @mht INT;
EXEC @hr = sp_OACreate 'Chilkat.Mht', @mht OUT;
IF @hr <> 0
BEGIN
    PRINT 'Failed to create Chilkat.Mht object';
    RETURN;
END

-- Set any necessary properties
EXEC sp_OASetProperty @mht, 'UseCids', 1;

-- Call the method; the output parameter is not used here
DECLARE @dummy NVARCHAR(4000);
EXEC sp_OAMethod @mht, 'GetEML', @dummy OUT, 'https://www.example.com/';

-- Check if the method succeeded
DECLARE @success INT;
EXEC sp_OAGetProperty @mht, 'LastMethodSuccess', @success OUT;
IF @success <> 1
BEGIN
    -- Retrieve and display the error text
    DECLARE @error TABLE (ErrorText NVARCHAR(MAX));
    INSERT INTO @error EXEC sp_OAGetProperty @mht, 'LastErrorText';
    SELECT * FROM @error;

    EXEC @hr = sp_OADestroy @mht;
    RETURN;
END

Step 3: Retrieve the Full Result

Since the result may exceed the size limit of local variables, use a temporary table to capture the full string from LastStringResult.

DECLARE @result TABLE (EMLContent NVARCHAR(MAX));
INSERT INTO @result EXEC sp_OAGetProperty @mht, 'LastStringResult';
SELECT * FROM @result;

EXEC @hr = sp_OADestroy @mht;

Important Considerations

• Avoid Using NVARCHAR(MAX) for Output Parameters: When calling sp_OAMethod, do not declare the output parameter as NVARCHAR(MAX). Instead, use a dummy variable (e.g., @dummy NVARCHAR(4000)) and retrieve the full result from LastStringResult.
• Use Temporary Tables for Large Strings: To handle strings exceeding 4,000 characters, insert the result into a temporary table with a column defined as NVARCHAR(MAX).
• Check LastMethodSuccess: Always verify the success of the method call by checking the LastMethodSuccess property before attempting to retrieve LastStringResult.

By following this approach, you can effectively handle long strings returned by Chilkat ActiveX methods within SQL Server stored procedures.