Avatar of careybroph
careybroph
Flag for United States of America

asked on 

MS SQL API Return String Size Limit?

OS: Windows Server
SQL: MS SQL 2019 Pro

I have an SQL routine that makes an API call to an external program. It works beautifully... UNTIL... the api return string exceeds 4000 characters, at which point, it fails. It's pretty basic. And, as I said, it works fine unless the return string is too big. As you can see, the api return content is being saved to a field that has a length 8000, so that's not the issue. I just updated to MS SQL 2019 Pro thinking (hoping!) that this limitation would have been resolved. Wondering if there is some internal SQL setting that could be increased. No idea why it is capped at 4000 characters in SQL. It works just find in any other platform. Is there a better/different way to do a simple API call from SQL that might get around this limitation?

ALTER FUNCTION [dbo].[RC_API_GetTargetRecs]()
RETURNS VarChar(8000)
AS
BEGIN
    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);
    Declare @Body as nvarchar(2000);

SET @Body = '&token=XXXXXXXXXXX' (real token removed)
NOTE: I've deleted the rest of @Body structure, as it's API parameters that work just fine until it hits 4000 characters.

    Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
    Exec  sp_OAMethod @Object, 'open', NULL, 'post','[api path - removed]', 'false'

    Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
    Exec sp_OAMethod @Object, 'send', null, @body

    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    Exec sp_OADestroy @Object

RETURN @ResponseText
END

GO
Microsoft SQL Server

Avatar of undefined
Last Comment
careybroph

8/22/2022 - Mon