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'))

PRINT @RESULT;

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.

There are a handful of examples out on the internet on how to use SSRS from an ASP.NET website but all of the ones I came across seemed like hacks. They range from throwing an ASPX page with a ReportViewer control to complex JavaScript hacks. The following method is the one I have used for many years that adheres to the true MVC manner. This method connects to SSRS through the web service using Microsoft.Reporting.WebForms assembly.

Continue reading

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.

Continue reading