If you’ll ever try to generate JSON file using Sql Server’s Management Studio, probably there’ll be lots of pain in your future. If you wanna save the pain, you can use SQL2JSON, an open source project I started last week.
The idea was born out of a need to generate a huge JSON file containing data from MSSQL database. After seeing our DBA struggling with formatting, escaping weird characters and unicode strings, I decided to write a small utility that will do just that - execute a sql query and capture its results as a json file. You can grab it from Github repository.
Suppose you have a table called “users” with the following records:
1 2 3 4 5