1

I am trying to update a column in a SQL Server DB from C# that is defined as nVarChar(max). The update works as long as the text length is 4000 characters or less. As soon as the length is 4001 I get the infamous error:

String or binary data would be truncated.
The statement has been terminated.

The update will work if it is run manually from within SQL Server Management Studio.

The C# code calls a SP to perform the update. I listed all the relevant code below:

CREATE TABLE [dbo].[UpdateTable]
(
    [PK_ID] [int] IDENTITY(1,1) NOT NULL,
    [NEWTEXT] [nvarchar](max) NULL,

    CONSTRAINT [PK_UpdateTable] 
       PRIMARY KEY CLUSTERED  ([PK_ID] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--DATABASE STORED PROCEDURE
CREATE Procedure [dbo].[usp_UpdateText]
    @ID int,
    @NEWTEXT nvarchar (max)    
AS
    SET NOCOUNT ON

    UPDATE [UpdateTable] 
    SET [NEWTEXT] = @NEWTEXT 
    WHERE PK_ID = @ID;

    RETURN

C# DB code:

using System.Data;
using System.Data.SqlClient;

public static void UpdateText(AjaxObjects.TextObject obj)
{
    SqlConnection connection = DataBase.GetConnection();

    using (connection)
    {
        connection.Open();

        using (SqlCommand cmd = new SqlCommand("usp_UpdateText", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = obj.Id;
            cmd.Parameters.Add("@NEWTEXT", SqlDbType.NVarChar, -1).Value = obj.Text;
            cmd.ExecuteNonQuery();
        }

        connection.Close();
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shanew44
  • 19
  • 1
  • 3
  • 2
    Check the sqlparameter structure in the debugger. It may be defaulting to 4000 length. – Martin Noreke Jun 10 '15 at 22:49
  • Fixed the problem! The code above was all correct. The problem was with a secondary SP call that logged the DB action into a log table. The log table did not have the column defined as nvarchar(max) so it was failing at 4000 characters! – Shanew44 Jun 10 '15 at 23:48
  • Check out this useful answer [Solution for large string in sql](http://stackoverflow.com/questions/23226235/sql-trying-to-insert-large-string-into-varcharmax) – Yusufm.Salh Mar 20 '16 at 17:25

0 Answers0