Last night I spent hours debugging a situation where a result from DB['select huge_nvarchar from table'] was being truncated to 32256 characters.

I tried searching for people having this problem tossing all sorts of combinations of %w( tiny_tds sequel ruby sqlserver mssql nvarchar truncate 32256 ) about and got nowhere. Even things like nvarchar 32256, which I assumed would orient me in a new, fruitful direction led nowhere at all. These terms just don’t live together on m/any pages.

Finally I took one last stab with freetds 32256 and hit the jackpot:

Google Search freetds 32256

It was obvious this fella Matt also struggled, and wrote a short blog post with a quick synopsis of the problem and solution (not unlike this) for future him, and future you.

So, finally. If you find that your nvarchar(max) strings are being truncated (with any DB client) it’s likely due to a connection setting that you can change with a simple SQL statement: SET TEXTSIZE { number }: Reference

number Is the length of varchar(max), nvarchar(max), varbinary(max), text, ntext, or image data, in bytes. number is an integer with a maximum value of 2147483647 (2 GB). A value of -1 indicates unlimited size. A value of 0 resets the size to the default value of 4 KB.

A sample setting the max nvarchar length with Sequel:

DB = Sequel.connect 'tinytds://user:password@host/db'

# Set the max size returned:
DB.execute 'SET TEXTSIZE 1000000000' # Arbitrary value that I know is bigger than my max(len(value_of_interest))

results = DB['select huge_nvarchar from table']
my_huge_values = results.map {|row| JSON.parse row[:huge_nvarchar] }
# No errors!

or TinyTDS natively:

client = TinyTds::Client.new(connection_params)

# Let's set our length to Wild West mode. Unlimited data! (Okay, 2GB...)
client.execute('SET TEXTSIZE -1').do # It will return -1, that's okay though since this command didn't affect any rows.

results = client.execute 'select huge_nvarchar from table'
my_huge_values = results.map {|row| JSON.parse row["huge_nvarchar"] }
# No errors!

It’s obvious I’m trying to load up the search terms in this post, since it was so hard to find the solution last night. So, just to recap real quick:

If you find yourself trying to load nvarchar(max) from your MS SQL Server database using ruby, freetds, tinytds, sequel, or any other database client, by default, your string may be getting truncated at length 32256. When working with larger data sets, such as logged JSON responses from web requests that you wish to parse, JSON.parse(response) is going to have a bad day when { can’t find it’s matching }:

/home/ttilberg/.rvm/gems/ruby-2.2.5/gems/json-1.8.6/lib/json/common.rb:155:in `parse': 419: unexpected token at '{"synonym":false,"value":"1-VOLKSWA' (JSON::ParserError)
        from /home/ttilberg/.rvm/gems/ruby-2.2.5/gems/json-1.8.6/lib/json/common.rb:155:in `parse'

Thanks Matt, at mattstabeler.co.uk.