SQL sp_OAMethod String Length Return Value Limitations

The sp_OAMethod function is used to call ActiveX methods in SQL stored procedures.  If the ActiveX method returns a string, there is a limit imposed by sp_OAMethod on the size of the string that can be returned.  (Perhaps it is 4000 chars?)

This blog post describes a way to workaround this limitation.

An example of a method call that is likely to return a string longer than 4000 chars is the HTTP method to GET a web page.  For example:

DECLARE @html nvarchar(max)
EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://www.paypal.com/'

The workaround is to tell Chilkat to internally save the last string returned by the last method called from any Chilkat object.   Then you can access the LastStringResult property using a temp table.

The 1st step is to tell Chilkat to save each string return value in the LastStringResult property.

       DECLARE @hr int
       DECLARE @global int
       EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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

Now we can make the Chilkat method call and ignore the string returned by the method and instead get it from the LastStringResult property. For example:

CREATE PROC ChilkatSetup
AS
BEGIN
    -- Checking return values for success omitted for brevity.
    -- Call ChilkatSetup at the start of each stored procedure.
    DECLARE @glob int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Global', @glob OUT

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

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int

    -- Unlock and set KeepStringResult
    EXEC ChilkatSetup

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

    -- Send the HTTP GET, but we'll initially ignore the return value.
    DECLARE @html nvarchar(max)
    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'https://www.paypal.com/'

    -- Check LastMethodSuccess to see if the method succeeded.
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @success OUT
    IF @success <> 1
      BEGIN
       -- The LastErrorText may be very large, so we should access it through a temp table..
       DECLARE @tmp1 TABLE (lastErrText ntext)
       INSERT INTO @tmp1 EXEC sp_OAGetProperty @http, 'LastErrorText'
       SELECT * from @tmp1

        EXEC @hr = sp_OADestroy @http
        RETURN
      END

   -- The Chilkat method succeeded.  Now get the return string from LastStringResult via a temp table.
   DECLARE @tmp2 TABLE (res ntext)
   INSERT INTO @tmp2 EXEC sp_OAGetProperty @http, 'LastStringResult'
   SELECT @html=res from @tmp2

   PRINT @html

   EXEC @hr = sp_OADestroy @http

END
GO