Query failed: Array dimensions exceeded supported range.

Question

I have a query that fails to execute and returns the message "Query failed: Array dimensions exceeded supported range." with no further explanation. It seems like it may be maxing out a resource somewhere but I don't know where. The query is a complex SELECT formated as JSON (FOR JSON) and can return a large amount of text (about 2 GB of text when written to file).

Answer 1

Thta is an out-of-memory error returned by the serializer/deserializer. Try to split the output of the query into X json files.

Alternatively you can output to a varchar(max)

SELECT CAST((SELECT [Columns] FROM [Tables] FOR JSON ....  AS VARCHAR(MAX)) AS JSONDATA  

Or send the output to a temporary table also.

 declare @json table (col1 nvarchar(max));  
 insert into @json   
 select ....