SSMS and sp_OAMethod: is there a Data Type greater than VARCHAR(8000)?

Question

Calling a REST service through SSMS is indeed not a great idea.

BTW since Microsoft created the Stored Procedure sp_OAMethod and even Phil Factor from Red Gate show us how to use it I wanted to give it a go.

I wanted to import some data from OpenStreetMap directly into MSSQL, so I copy a good query from here and I adapt it to my whish.

In this example I'm returning all Cinemas in Nelson:

DECLARE @obj AS INT
DECLARE @Uri AS NVARCHAR(4000)
DECLARE @Response AS VARCHAR(8000)

SET @Uri = 'http://overpass-api.de/api/interpreter?data=area[name="Nelson"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Uri, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

SELECT @Response [response] 
EXEC sp_OADestroy @obj

Nice and easy, I can see the REST call response in Postman as well as in SSMS:

Problem starts when I try to retrieve all Cinemas from a bigger city like Auckland:

DECLARE @obj AS INT
DECLARE @Uri AS NVARCHAR(4000)
DECLARE @Response AS VARCHAR(8000)

SET @Uri = 'http://overpass-api.de/api/interpreter?data=area[name="Auckland"]->.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Uri, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

SELECT @Response [response] 
EXEC sp_OADestroy @obj

The REST call is retrieving more data and the variable @Response AS VARCHAR(8000) cannot hold all the data:

Of course I tried to use DECLARE @Response AS VARCHAR(MAX) but this won't help neither.

Shouldn't VARCHAR(MAX) hold 65,535 characters and till 2GB of data?

What should I use instead?

Is there a way to split data and concatenate later?

EDIT: I think I'm getting closer: I can use OPENJSON this way, but I still don't know how to structure the query... any help would be appreciated

I would use SQLCLR, not sp_OA%. sp_OA is simply not safe to use — gbn, Oct 01 '18 at 10:10
Thank you but this is not the point: even using `SQLCLR` the problem remains because I cannot store all the result inside the datatype `VARCHAR(MAX)`. The maximum stored value for `VARCHAR(MAX)` is 4000 characters but I have 6000. Do you know a bigger datatype? — Francesco Mantovani, Oct 02 '18 at 09:33
VARCHAR(MAX) *is* 2GB. varchar(n) is max 8000. varchar(8000) is different to varchar(max) in storage and some other behaviours. If you want more than 8000 characters in a column or variable, use VARCHAR(MAX) — gbn, Oct 02 '18 at 12:05
Man, I'm telling you that VARCHAR(MAX) cannot store more then 8000 characters. Try out yourself `DECLARE @str varchar(max) SET @str = REPLICATE('A', 4000) + REPLICATE('B', 4000) + REPLICATE('C', 4000) PRINT LEN(@str)` — Francesco Mantovani, Oct 02 '18 at 23:22
You're doing it *wrong*. See https://stackoverflow.com/a/1371584/27535 for why. If you still disagree, then contact go to Microsoft guthub and update the documentation here https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017 — gbn, Oct 03 '18 at 06:39

score 6 · Accepted Answer · answered Oct 07 '18 at 08:45

I'm clapping the hands to myself.

I admit, it's a nightmare solution but it get things done. The solution was to set:

Declare @Response as table(Json_Table nvarchar(max))

This way I created a table with a data type which has nvarchar(max) and now yes, it can hold 65,535 characters and till 2GB of data.

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

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
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'
Exec @hr=sp_OAMethod @Object, 'send'
Exec @hr=sp_OAMethod @Object, 'responseText', @Response OUTPUT

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

select * from @Response

EXEC sp_OADestroy @Object

Please post if you find a better solution, it will be much appreciated.

Thank you, this is truly hideous but it saved me an hour having to re-write ancient code into a CLR — Adam Davies, Mar 16 '21 at 09:59

gbn · Answer 2 · 2018-10-03T07:09:05.437

Wrong code:

DECLARE @str varchar(max)
SET @str = REPLICATE('A', 4000) + REPLICATE('B', 4000) + REPLICATE('C', 4000)
PRINT LEN(@str)

From REPLICATE in SQL Server docs

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

Correct code:

DECLARE @str varchar(max)
DECLARE @seed varchar(max) = 'A'

SET @str = REPLICATE(@seed, 4000) + REPLICATE(@seed, 4000) + REPLICATE(@seed, 4000)
PRINT LEN(@str)

--12000

For other answers, see For Nvarchar(Max) I am only getting 4000 characters in TSQL?

Boom. you gave a great idea. I will label it as the answer if I cannot find something better. Thank you for your help — Francesco Mantovani, Oct 03 '18 at 21:09

SSMS and sp_OAMethod: is there a Data Type greater than VARCHAR(8000)?

2 Answers2

Linked