troubleshooting Question

MS SQL API Return String Size Limit?

Avatar of careybroph
careybrophFlag for United States of America asked on
Microsoft SQL Server
3 Comments2 Solutions17 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Éric Moreau
Senior .Net Consultant
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros