Text is being truncated on Insert into SQL Server database

Question

We have a strange problem using SqlCommand to insert data into a SQL Server database. If we try to insert a large text into the column konnotiz1, the text is being truncated after 43245 characters. For some reason I don't understand the real application code creates 42909 characters.

Here's a screenshot of the column properties page:

SQL-Server Management Studio propertypage

The following code causes the problem:

static void Test()
{
    using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(getConString()))
    {
        con.Open();
        System.Data.SqlClient.SqlCommand cmd = con.CreateCommand();
        cmd.CommandText = "Insert into kontakte (konlfdnr, konNotiz1) values (@konlfdnr, @konNotiz1)";
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 60;

        // you can ignore this param, it's owner pk-column. 
        System.Data.SqlClient.SqlParameter param = cmd.Parameters.Add("@konlfdnr", SqlDbType.Char, 10);

        // better don't ask why we have to pad this... ;) 
        param.Value = "1".PadLeft(10);
        param.Direction = ParameterDirection.Input;

        param = cmd.Parameters.Add("@konNotiz1", SqlDbType.Text);
        param.Direction = ParameterDirection.Input;
        param.Value = getParamValue();

        cmd.ExecuteNonQuery();
        con.Close();
    }
}

private static string getParamValue()
{
     // my Textfile has something about 300000 characters. It's a html code from a mail.
     return System.IO.File.ReadAllText("C:\\Temp\\insert.txt");
}

private static string getConString()
{ 
    return @"Data Source=NB-JH1\SQLEXPRESS;Initial Catalog=Testsystem_Local;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;Pooling=True;MultipleActiveResultSets=True;
}

Has anyone an idea, why the text is being truncated and how we can get this working?

This [article](http://www.mssqltips.com/sqlservertip/2857/silent-truncation-of-sql-server-data-inserts/) will enlighten you better. — chridam, Mar 03 '14 at 15:20
have you specified a size on the parameter? Text can hold upto 2.1G bytes — T McKeown, Mar 03 '14 at 15:25
How are you measuring the length after the data has been loaded? — Gordon Linoff, Mar 03 '14 at 15:26
param = cmd.Parameters.Add("@konNotiz1", SqlDbType.VarChar, -1); — deramko, Mar 03 '14 at 15:27
What length of data is read in from the file? Are you actually sending all data into SQL Server before you blame that... — gbn, Mar 03 '14 at 15:55
@TMcKeown Nope. I tried to set it, but it doesn't change the result. I tried -1 and the length of the text — Jürgen Hoffmann, Mar 04 '14 at 10:36
@GordonLinoff I select the data from database using SQL-Management Studio, copy it for e.g. to the Notepad or select it by code and get the length there. — Jürgen Hoffmann, Mar 04 '14 at 10:38
@gbn the file has 2656606 characters. I send the data with the code above. I hope this sends all data. At least I don't know why it shouldnot be sent. — Jürgen Hoffmann, Mar 04 '14 at 10:49

score 1 · Accepted Answer · answered Mar 04 '14 at 14:06

Your problem would seem to be a limit in SQL Server Management Studio, not in the database. Here is a bug report on the problem, which claims that the bug has been fixed in the most recent versions (I don't know if this is true). There are work-arounds, as suggested here. I have not personally tried them.

You should test the length by using the len() function (or something similar). This will let you know if all the data is there.

I have worked with very long strings in the database, passing them back and forth between tables, variables, and stored procedures so the database can handle the strings. The problem is in the interface that fetches them.

The result doesnt change, if I read the data by code. So it's not the limitation of the managemend Studio. But thx. for your hint. — Jürgen Hoffmann, Mar 05 '14 at 08:34

Shlomi Borovitz · Answer 2 · 2014-03-03T19:04:04.610

0

First, Text is an obsolete sql data type. Use varchar(max) instead. I would try to change the field's type, and the parameter's type.

This would not certainly fix the problem - but it's a good possibility to eliminate (that this what's causing the problem), and it's a good practice too (not to use a deprecated feature)

edited Mar 03 '14 at 19:04

answered Mar 03 '14 at 15:39

Shlomi Borovitz

1,700
9
9

1) Text is _deprecated_, not obsolete - it is still supported, and 2) since they are equivalent it probably won;t solve the problem. – D Stanley Mar 03 '14 at 18:39
1. Accepted. 2. They are equivalent by functionality, but not by implementation - so, it's a good advice to change the type to the not deprecated one, and maybe (not certainly), it even resolve the problem... I should have written that.. But forgot, since I'm writing from mobile. – Shlomi Borovitz Mar 03 '14 at 18:59
unfortunately I can't change the datatype. It's beeing used for years now and shipped to many customers. Some parts of our Software are written in VisualFoxPro, maybe that's the reason why they decided to use this type. – Jürgen Hoffmann Mar 04 '14 at 10:51
I just read something that make me think... How exactly do you know that the text is truncated? – Shlomi Borovitz Mar 04 '14 at 11:06
@ShlomiBorovitz I select the column by code or with the SQL-Managemend Studio and Compare it with the original value which I used for the insert. – Jürgen Hoffmann Mar 04 '14 at 12:40
Good! that exactly what I found. The management studio is the one which truncated the text (write a small program to retrieve the text, and store it into a file, and then compare the two). [Look here](http://bit.ly/MKL8m7) – Shlomi Borovitz Mar 04 '14 at 12:55

Text is being truncated on Insert into SQL Server database

2 Answers2