1

I'm writing a script which will pull data from one database to another with some alteration inbetween. MS SQL server is used on both sides, but I'm having some problems where the SELECT query doesn't return the full JSON string.

I've tried to use a lot of different query methods and functions, but it seems like the problems is not in how I query the information, but rather how the script recieves the JSON.

This is the latest query I've tried, nothing fancy, and it retrieves the information.


var fishQuery = "SELECT [Id],[Name],[OrgNumber] FROM Camps ORDER BY [Id] OFFSET 5 ROWS FETCH NEXT 1000 ROWS ONLY FOR JSON PATH";

EDIT: Should mention that this is how I treat the string after.

var campsInfo = fishConnection.Query<string>(fishQuery).First();
List<Camps> Camps = new List<Camps>();
Camps = (JsonConvert.DeserializeObject<List<Camps>>(campsInfo));

However, I get these Errors in Visual Studio:

Newtonsoft.Json.JsonReaderException: 'Unterminated string. Expected delimiter: ". Path '[20].Name', line 1, position 2033.'

If I print it to a file or Console I get 

(A lot more valid JSON data before this...) (...) {\"Id\":\"173246A1-8069-437C-8731-05DBE69C784F\",\"Name\":\"SKANSE"

Which shows that I get invalid JSON since the result stops "halfway" through. I've read some of these : How do you view ALL text from an ntext or nvarchar(max) in SSMS?

SQL Server - "for json path" statement does not return more than 2984 lines of JSON string

But I'm not keen on making changes too my SQL server, which is in production atm, before I know that I can fix it on my side!

Thanks for any help!

Anton G. Fagerbakk
  • 11
  • 3
  • 1
    You'll get back the results as multiple strings in multiple rows that need to be concatenated. See, e.g., [this](https://learn.microsoft.com/sql/relational-databases/json/use-for-json-output-in-sql-server-and-in-client-apps-sql-server). – Jeroen Mostert Jul 11 '19 at 12:01
  • @JeroenMostert You're totally right, somewhere along the lines I changed ```var campsInfo = fishConnection.Query(fishQuery).ToList(); too var campsInfo = fishConnection.Query(fishQuery).First();.``` – Anton G. Fagerbakk Jul 11 '19 at 12:33
  • `string.Concat(fishConnection.Query(fishQuery))` should do it, then. This is not very efficient with large JSON documents -- `SqlDataReader.GetTextReader()` should offer more efficient access, but I'm pretty sure that won't take care of the concatenation, so you'd need to write a custom `TextReader` that `JsonSerializer.Deserialize` can eat. (All this is assuming you have a reason to care about the efficiency, of course.) Note that if all you're doing is deserializing a flat rowset to objects, Dapper will be tons more efficient than going through JSON. – Jeroen Mostert Jul 11 '19 at 15:07
  • @JeroenMostert I wrote custom text reader and it works for now, but I'll probably have to refactor it later. Thanks! – Anton G. Fagerbakk Jul 12 '19 at 06:28

0 Answers0