SSMS: OPENJSON works but not with direct REST call

Question

Following this article I'm trying to implement a query that could retrieve data from a REST call and parse the JSON with OPENJSON.

I call this URL with Postman to check if the data are alright:

The data are there.

I copy paste the JSON and I test my call on SSMS:

DECLARE @json nvarchar(max) = N'{
    "version": 0.6,
    "generator": "Overpass API 0.7.55.4 3079d8ea",
    "osm3s": {
        "timestamp_osm_base": "2018-10-04T10:07:02Z",
        "timestamp_areas_base": "2018-10-04T09:02:02Z",
        "copyright": "The data included in this document is from www.openstreetmap.org. The data is made available under ODbL."
    },
    "elements": [
        {
            "type": "node",
            "id": 501591237,
            "lat": 49.4943882,
            "lon": -117.2910343,
            "tags": {
                "amenity": "cinema",
                "name": "Civic Theatre",
                "website": "http://civictheatre.ca"
            }
        },
        {
            "type": "node",
            "id": 2293276829,
            "lat": -41.2709865,
            "lon": 173.2842196,
            "tags": {
                "amenity": "cinema",
                "name": "Cinema 6"
            }
        }
    ]
}';



SELECT * FROM OPENJSON(@json, N'$.elements')
WITH (   
      [type] nvarchar(max) N'$.type'   ,
      [id]   nvarchar(max) N'$.id',
      [lat]   nvarchar(max) N'$.lat',
      [lon]   nvarchar(max) N'$.lon',
      [amenity]   nvarchar(max) N'$.tags.amenity',
      [name]   nvarchar(max) N'$.tags.name'

)

All good, is working: my query can parse the JSON.

I now follow this guide that shows me how to make a REST call within SSMS and I try to retrieve the data of the same URL and see them on-the-fly into SSMS:

Declare @Object as Int;
Declare @json as nvarchar(max);


Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Nelson%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @json OUTPUT

select @json

SELECT * FROM OPENJSON(@json, N'$.elements')
WITH (   
      [type] nvarchar(max) N'$.type'   ,
      [id]   nvarchar(max) N'$.id',
      [lat]   nvarchar(max) N'$.lat',
      [lon]   nvarchar(max) N'$.lon',
      [amenity]   nvarchar(max) N'$.tags.amenity',
      [name]   nvarchar(max) N'$.tags.name'

)

Exec sp_OADestroy @Object

Ouch! Is not working!

Why the data are empty? Declare @json as nvarchar(max); was working in the previous query. why is not working any more?

If I put Declare @json as varchar(8000); it works only because I'm retrieving less than 8000 characters but I cannot work this way.

If I'm not wrong OPENJSON was developed exactly to avoid such problem and load till 2GB of data.

I want to use nvarchar(max) and load a lot of data. This is the whole point of using OPENJSON.

SSMS is the client tool, it has nothing to do with the queries. OPENJSON has nothing to do with how its input is generated. And `sp_OAMethod` is *not* a good way to make HTTP calls. Apart from the risk of creating orphaned objects, it can't pass headers or handle responses. It was use *before* SQL Server 2005. If you have to make HTTP calls use SQLCLR at least. Better yet, make the calls from the client, not the database — Panagiotis Kanavos, Oct 04 '18 at 11:46
BTW the output means that nothing was returned from that HTTP call, not that OPENJSON failed. If an HTTP call fails the server returns a Status code and description that explain what's wrong, eg 401 is Unauthorized, 404 Not Found etc. There's no response text in these cases — Panagiotis Kanavos, Oct 04 '18 at 11:48
You need to retrieve the [status](https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms767625(v%3Dvs.85)) property, eg with `sp_OAMethod @Object, 'status'` and [statusText](https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms759127(v%3dvs.85)) to see why the call failed. — Panagiotis Kanavos, Oct 04 '18 at 11:58
@PanagiotisKanavos, you can POST `body` and `headers`. Unfortunately I cannot drop DLLs on the customer machine. I know is wrong but sp_OAMethod get the work done in seconds and is the only thing I can use — Francesco Mantovani, Oct 05 '18 at 20:28
As said, if I substitute `nvarchar(max)` with `varchar(8000)` I see the JSON in SSMS. Thank you for the tip, I used `sp_OAMethod @Object, 'status'` and yes, I have a 200. So the call is good. `varchar(8000)` can hold the JSON because can hold 8000 characters but `nvarchar(MAX)` can only hold 4000 characters. — Francesco Mantovani, Oct 05 '18 at 20:33
If so it means that `OPENJSON` can only read and store JSON till 2GB size from a file but not from a REST call and SQLCLR will fail as well because there is no data type that can store more than 8000 characters in MSSQL. Which is kind of awkward — Francesco Mantovani, Oct 05 '18 at 20:36
@PanagiotisKanavos, I'm not the only one: it seems SQL Server cannot do REST call after 8000 characters: https://blog.dotnetframework.org/2010/09/01/make-a-http-request-from-sql-server/ . Odd. — Francesco Mantovani, Oct 05 '18 at 22:02

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Done.

Thank you so much @PanagiotisKanavos for the suggestion of the status in order to catch the error.

The solution was to use Declare @json as table(Json_Table nvarchar(max)) and store the JSON into a table instead of a variable. It now can store 2GB!!!

Declare @Object as Int;
DECLARE @hr  int
Declare @json as table(Json_Table nvarchar(max))

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://overpass-api.de/api/interpreter?data=[out:json];area[name="Auckland"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
                 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

SELECT * FROM OPENJSON((select * from @json), N'$.elements')
WITH (   
      [type] nvarchar(max) N'$.type'   ,
      [id]   nvarchar(max) N'$.id',
      [lat]   nvarchar(max) N'$.lat',
      [lon]   nvarchar(max) N'$.lon',
      [amenity]   nvarchar(max) N'$.tags.amenity',
      [name]   nvarchar(max) N'$.tags.name'     
)
EXEC sp_OADestroy @Object

Is this the right way to get things done?

Hell no! I shouldn't use sp_OAMethod and I should use SQLCLR instead, or any other external process written in Python, PowerShell, VB, C#, etc...

But, you know, I should also drink less beer and buy less bacon but...YOLO, whatever works... just do it.

Thanks to this I can now create a stored procedure that makes a REST call and automatically save data into SQL Server. I hunted the solution for days and I finally found the answer here.

I hope this will help, I see there are a few hundred people searching for this reply out there.

SSMS: OPENJSON works but not with direct REST call

1 Answers1