locked
T-SQL - ODSOLE Extended Procedure The source data specified for this string or binary column or parameter is too long RRS feed

  • Question

  • I goal is to connect to a webapi and request data.

    I am quite sure that I am connected and I am getting data back because the error message indicated that there is to much data to store. If my @url + @respondsbody is set correctly I should not get more then 2500 characters.

    Error:

    DECLARE 
        @object int,
        @hr int,
        @src varchar(8000),
        @desc varchar(8000),
        @authHeader NVARCHAR(4000),
        @contentType NVARCHAR(4000),
        @postData NVARCHAR(2000),
        @responseText NVARCHAR(4000),
        @responseXML NVARCHAR(4000),
        @ret INT,
        @status NVARCHAR(4000),
        @statusText NVARCHAR(4000),
        @token INT,
        @methodName varchar(50) = 'GET',
        @requestBody varchar(8000) = '$select=*&limit=1',
        @UserName nvarchar(100) = 'dummy',
        @Password nvarchar(100) = 'dummy' 
    DECLARE 
        @url NVARCHAR(4000) = 'https://restapi.dummy.com/prod/rest/api/v1/01/products';
    DECLARE
        @json AS TABLE(Json_Table NVARCHAR(4000));
    
    
    
    SET NOCOUNT ON
    IF    @methodName = ''
        BEGIN
            select FailPoint = 'Method Name must be set'
            return
        END
    
    -- Set Authentications
    SET @authHeader = 'BASIC 123456789';
    SET @contentType = 'application/json';
    
    -- Open a connection
    EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
        IF @ret <> 0 
            BEGIN  
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT   
                RAISERROR('Error Creating COM Component 0x%x, %s, %s',16,1, @hr, @src, @desc)  
                RETURN  
            END;  
    
    -- make a request
    EXEC @ret = sp_OAMethod @token, 'open', NULL, @methodName, @url, 'false', @UserName, @Password;
        IF @ret <> 0 
            BEGIN
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
                SELECT      hResult = convert(varbinary(4), @ret), 
                    source = @src,
                    description = @desc,
                    FailPoint = 'Open failed',
                    MethodName = @methodName 
                goto destroy 
                return
            END
    
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
        IF @ret <> 0 
            BEGIN
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
                SELECT      hResult = convert(varbinary(4), @ret), 
                    source = @src,
                    description = @desc,
                    FailPoint = 'SetRequestHeader failed: Authentication',
                    MethodName = @methodName 
                goto destroy 
                return
            END
    
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
        IF @ret <> 0 
            BEGIN
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
                SELECT      hResult = convert(varbinary(4), @ret), 
                    source = @src,
                    description = @desc,
                    FailPoint = 'SetRequestHeader failed: Content-type',
                    MethodName = @methodName 
                goto destroy 
                return
            END
    
    DECLARE
        @len int
        SET @len = len(@requestBody);
    
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', null, 'Content-Length', @len;
    IF @ret <> 0 
            BEGIN
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
                SELECT      hResult = convert(varbinary(4), @ret), 
                    source = @src,
                    description = @desc,
                    FailPoint = 'SetRequestHeader failed: Content-Length',
                    MethodName = @methodName 
                goto destroy 
                return
            END
    
    EXEC @ret = sp_OAMethod @token, 'send', null, @requestBody
        IF @ret <> 0 
            BEGIN
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
                SELECT      hResult = convert(varbinary(4), @ret), 
                    source = @src,
                    description = @desc,
                    FailPoint = 'Send failed',
                    MethodName = @methodName 
                goto destroy 
                return
            END
    
    -- Handle responce
    EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
    EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
    EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
        IF @ret <> 0 
            BEGIN
                EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
                SELECT      hResult = convert(varbinary(4), @ret), 
                    source = @src,
                    description = @desc,
                    FailPoint = 'ResponseText failed',
                    MethodName = @methodName 
                goto destroy 
                return
            END
    
    -- Print response
    --PRINT 'Status: ' + @status + ' (' + @statusText + ')';
    --PRINT 'Response text: ' + @responseText;
    
    -- Grab the responseText property, and insert the JSON string into a table temporarily. This is very important, if you don't do this step you'll run into problems.
    INSERT into @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText'
    --Select the JSON string from the Table we just inserted it into. You'll also be able to see the entire string with this statement.
    SELECT * FROM @json
    
    DESTROY: 
    
    EXEC sp_OADestroy @token 
    
    SET NOCOUNT OFF

    Tuesday, August 4, 2020 12:07 PM

All replies

  • At my client we actually to REST calls from SQL Server, but we use the CLR rather than relying on sp_OAxxxxx, and I can only recommend that you take the same path. With the CLR, you can easily first write the code in a console-mode program to see that the REST call works, and then you can embed in a CLR stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 4, 2020 9:53 PM
  • Hi Phyxius,

    Please try this:

    DECLARE @json AS TABLE(Json_Table NVARCHAR(MAX));

    Best Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 5, 2020 8:22 AM
  • Hi,

    I am planning to use CLR in the future.

    Changing the data type variable from 4000 to max was the first step.

    The final blow was to comment out the following IF statement.

    Maybe someone can enlighten me why this statement is not working as expected.

    EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
        --IF @ret <> 0 
            --BEGIN
            --    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
            --    SELECT      hResult = convert(varbinary(4), @ret), 
            --        source = @src,
            --        description = @desc,
            --        FailPoint = 'ResponseText failed',
            --        MethodName = @methodName 
            --    goto destroy 
            --    return
            --END

    Thursday, August 13, 2020 8:47 AM
  • Maybe someone can enlighten me why this statement is not working as expected.

    Obviously, if you don't check for error messages, you will not see any.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 13, 2020 8:53 AM