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