Help on json extract from API from TSQL

naza 21 Reputation points


I am trying to download data from an external web based application into my SQL Server through TSQL( as a first preference over SSIS/ Powershell) ).
I am very new to API and have been using the script provided by :

I have generated an OAuth access token key for my application , and marked it in my url

However I am not able to see any output.
The @ResponseText (variable marked in the script from the above referred url) is returning blank when declaring @ResponseText as nVarchar(4000);
The @ResponseText is returning null when declaring @ResponseText as nVarchar(max);

DECLARE @URL NVARCHAR(MAX) = 'https://<the website>.com/api/v1/me';  
Declare @Object as Int;  
Declare @ResponseText as nVarchar(4000);  
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;  
Exec sp_OAMethod @Object, 'open', NULL, 'get',  
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'authentication', 'OAuth <accesstoken>'  
Exec sp_OAMethod @Object, 'send'  
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT  
Select @ResponseText  
exec  @ret=sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT  
SELECT @ret  

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,050 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 42,381 Reputation points

    You select @ret, that's the numeric return code of the stored procedure sp_OAMethod: 0 = succes, otherwise a failure.
    The API call result should be in variable @ResponseText

    select @ResponseText  
    2 people found this answer helpful.