3

I am having issues saving very long strings to the database using NHibernate. The strings are being truncated to 43,680 characters. The string is a very long XML packet that varies in size, but sometimes is very long.

The database data type is nvarchar(max) so the limitation is not there.

Can somebody help me understand why NHibernate is truncating this, and how I can prevent it?

Here is my mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
  assembly="Application.DataObjects"
  namespace="Company.Application.DataObjects.Transaction.Domain">

  <class name="TransactionDO" table="Transactions">
    <id name="TransactionID">
    </id>
    <property name="Created" />
    <property name="LongXML"  type="StringClob">
      <column name="LongXML" sql-type="nvarchar(max)"/>
    </property>
    <property name="ProcessConstructor" />
    <property name="VeryLongXML" type="StringClob">
      <column name="VeryLongXML" sql-type="nvarchar(max)" />
    </property>
  </class>
</hibernate-mapping>
Luke Hutton
  • 10,612
  • 6
  • 33
  • 58
TPaul1981
  • 33
  • 3
  • What version of NHibernate are you using? Can try specify the max length for testing to see if works: ` ` nvarchar(max), in this case the length of the data can be 2^31-1 bytes – Luke Hutton Mar 07 '13 at 18:31
  • Sorry I forgot to add that. The version is 3.2. I have tried to specify the string length of 100000. and it still returned the same number of characters. – TPaul1981 Mar 07 '13 at 19:48
  • How are you verifying the strings are limited to 43,680 characters? There's a known bug in `Sql Server Management Studio 2008` that only show's strings at this limit in the grid, workarounds known. – Luke Hutton Mar 07 '13 at 20:42
  • That was the problem. Thank you for that. I didn't think about Management Studio being the issue. – TPaul1981 Mar 08 '13 at 03:14

2 Answers2

4

As noted in the comments in the OP's question, the issue was in SSMS displaying the data.

There is a known bug in Sql Server Management Studio 2008 that you cannot paste more than 43679 characters from a column in Grid Mode.

Options (Query Results/SQL Server/Results to Grid Page) version SQL Server 2008 R2

The maximum characters retrieved in Grid Mode for non XML data should be 65535 characters.

Severals ways have been discussed to display the XML through SSMS:

  • Set Maximum Characters Retrieved to XML data and set the limit to Unlimited.

    Try casting the result to XML, i.e. cast(COLUMN_NAME as XML)

  • Breaking output of column to multiple rows
  • Copy-paste column to another program
  • Third party tooling (ssmsboost)

Alternatively, these discussions might be useful:

https://stackoverflow.com/a/5508193/368552, https://stackoverflow.com/a/2760023/368552

Community
  • 1
  • 1
Luke Hutton
  • 10,612
  • 6
  • 33
  • 58
1

You can add length property and set it to max length ("2147483647") to ensure full storage of xml string; like

<property name="VeryLongXML" type="StringClob" length="2147483647">
Abhijit_Srikumar
  • 106
  • 1
  • 13