Is command length limited by SQLCommand?

  • peo_gt

    SSC Enthusiast

    Points: 162

    I have a table with a varchar(max) column to store a large configuration text string (it really a xml file but I need to have in a table).

    Working in .Net I use an SQLCommand object to insert the data.

    A varchar(max) field is supposed to take 2GB of data, but when my data exceeds about 65000 characters, the field remains blank.

    The key colums are inserted, no error is detected, the varcahr(max) column just remains blank.

    If I truncate my data string to 65000 it works fine, but 70000 does not (I have not tried 65535 :-))

    Server is 2008R2 Express

    /PeO

  • peo_gt

    SSC Enthusiast

    Points: 162

    It is SQL Server Management Studio that doesn't show data longer than 65535 bytes.

    A "real" readback showed that the data was in the table as expected.

    /PeO

  • Jim McLeod

    SSCarpal Tunnel

    Points: 4057

    Yep - you got it. There's a setting in Management Studio - Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved - Non XML Data, which defaults to 64KB.

    The maximum batch size is 65536 * Network Packet Size (sp_configure 'network packet size (B)'), which is usually 4 KB, but can be 0.5-32KB. At the default, this means a maximum batch size of 256 MB. At the maximum, 2 GB.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply