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
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 3 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 3 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004