SQL Server - sp_OAGetProperty returns nothing

Question

I have the below code that calls a link and it return some information in a json structure:

DECLARE 
@Object INT, 
@vResponseText varchar(8000),
@CountryInfo varchar(8000),
@GetCountryRequest AS varchar(8000)= 
'{   
   "userName":"200900119",
   "password":"8481774916"
}'



EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUTPUT

EXEC sp_OAMethod @Object, 'Open', NULL, 'POST', 'https://api.dpd.ro/v1/location/country/642', 'false'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'Send', NULL, @GetCountryRequest
EXEC sp_OAMethod @Object, 'responseText', @vResponseText OUTPUT

EXEC sp_OADestroy @Object

PRINT @vResponseText 

So far so good! If I would like to insert the response into a table and then use JSON_QUERY to manipulate the data, I need to insert it into a table (a global temporal table in my case), using GetProperty method, which is not working.

IF OBJECT_ID('tempdb.dbo.##CountryTable', 'U') IS NOT NULL
BEGIN
      DROP TABLE ##CountryTable
END

CREATE TABLE ##CountryTable(
CountryInfo varchar(8000)
)

INSERT ##CountryTable (CountryInfo)

EXEC sp_OAGetProperty @Object, 'responseText' 

EXEC sp_OADestroy @Object

What am I missing? Thanks

Answer 1

Take a look here: http://www.sqlservercentral.com/articles/JSON/141175/. It might help you getting to the result you are looking for.