In the newer versions of Microsoft SQL Server, it is now super easy to return selected rows as json data. Unfortunately, SSMS seems to truncate json strings that are longer than 2033 chars no matter what you do. This seems to happen no matter if you show the results in the grid, text, or a file.
Here is a workaround using a variable and `PRINT` if you want to just get the json string on your clipboard:
DECLARE @RESULT NVARCHAR(MAX);
SET @RESULT = (SELECT * FROM TABLE FOR JSON AUTO, ROOT('Data'))
Otherwise, you could always download Azure Data Studio which seems to show results without truncation issues. If you are unfamiliar with ADS, it is basically a multi-platform re-write of SSMS.
In my database course at Marshall University, I helped my professor with a side project involving Cancer research data that has been collected since 1973 by the National Cancer Institute. My first job was to take the SEER data and load it into a postgreSQL database.
The job was simple enough. The only problem? The data files were in SAS format. Basically the format was CRLF delimited entries composed of fixed-length fields. Here is a list of the fields and lengths that I used for the script.
I received metadata for a SQL Server 2008 R2 database which contained the system_type_id but not the data type itself. I ran a bunch of queries and logged the results to obtain a list of system_type_ids and the corresponding datatypes.
I looked on MSDN and was unable to find a list so I compiled one for usage for you google-ninjas who may find it handy.