Getting full text data out of SQL Server

If you're using SQL Server, and it's management tool SQL Server Management Studio one problem you may come across is that trying to get the full text value of a TEXT or VARCHAR(MAX) column does not work. Management studio limits you to at most 65,535 characters of text data. I have no idea why they impose this limit, or why you can get more if the data is XML rather than text, but that's how it is.

There are various workarounds on the internet for this such as converting the data into XML within a CDATA section. This can result in XML special characters being escaped however which can be a pain.

Another way to get at all the text that does not require any third-party tools or custom scripting is to make use of the sqlcmd command line program. This program can successfully query the full text value and dump the output either to the console or to a file for easy access.

Open a command prompt and use the following command to run your query:

sqlcmd -S . -d database -u -y 0 -Q "SELECT YourTextColumn FROM YourTable WHERE Whatever=Condition"

Replace the fake query with your actual query of course. Your text data will be dumped to the console. To send it to a file you can add the -o file.txt option onto the end. The output will include the affected rows count. If you'd like to exclude this, prefix your query with SET NOCOUNT ON;

If you get an error about sqlcmd not being a recognized command then you'll need to either add its directory to your PATH or reference it with an absolute path. The path to where the command is will vary by version, but it should be something like this: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\. Spend some time looking around or searching your SQL Server installation directory, and you should find it.

If you don't find it anywhere, you can download it from microsoft. If you'd like to find out more about this handy tool, check out its documentation.