none
Size of nvarchar(max) type

    Question

  • Hi,

    I am trying to store a text message thread into a db-field of type nvarchar(max). The problem :it truncates all characters going beyond the size of 1024.

    In short, the field is not storing more then 1024 characters in the cell. I learned that nvarchar(MAX) stores upto 2GB data into a cell.

    Other details are :

    I am developing a website which has messageing facility for its users. So to keep thread of message I am required to store huge data.

    Data format is simple text. To show/input the data i am using ASP.NET textbox with multi line selected on and maximum length set to '0' (means maximum), ideally it should allow upto 2GB of data entry. While typing the data i can see data sizing exceeding 1024 characters, but when it go to store the data into database, it stores only 1024 characters.

    What to do....

    I dont know what is happening as it does not show any error message before truncating the content of cell.

    Please help.

     

    Tuesday, April 27, 2010 1:08 PM

Answers

  • Hi,

    The message string may be truncated by some other code, please double check your code more carefully, also including the connection string.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, April 29, 2010 8:59 AM
  • hi,

    I'm currently on the idea of ChunSong Feng.. can you please try specifying the "size" of the parameter at (Ado.Net) parameter generation, like

    Dim paramMsgText As SqlParameter = _
                New SqlParameter("@MsgText", _
                   SqlDbType.NVarChar, -1)
            paramMsgText.Direction = ParameterDirection.Input
            command.Parameters.Add(paramMsgText)

    where "-1" explicitely indicates a "MAX" sized value?

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Thursday, April 29, 2010 3:29 PM

All replies

  • Are you using ADO.NET, LINQ to SQL or Entity Framework (or something else) for data access?

    Please provide some sample code for us to help you.


    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Tuesday, April 27, 2010 4:02 PM
  • Following is the part of code I am writing

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    ...
    public partial class ComposeMessage : System.Web.UI.Page
    {
    SqlConnection dbConnection = new SqlConnection();
    ...
    protected void Page_Load(object sender, EventArgs e)
    {
    ...
    dbConnection.ConnectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["CIS_DB"].ConnectionString);
    ...
    }
    
      protected void SendMsgBtn_Click(object sender, EventArgs e)
      {
        SqlCommand dbCommand = new SqlCommand();
    
        try
        {
          dbConnection.Open();
          dbCommand.Connection = dbConnection;
          dbCommand.CommandType = CommandType.StoredProcedure;
          dbCommand.CommandText = "sp_NewMessageEntry";
          dbCommand.Parameters.AddWithValue("@MsgFrom", loginName);
          dbCommand.Parameters.AddWithValue("@MsgTo", MsgToDDL.SelectedValue);
          dbCommand.Parameters.AddWithValue("@MsgDate", System.DateTime.Now);
          dbCommand.Parameters.AddWithValue("@MsgSubject", SubjectTXT.Text);
          dbCommand.Parameters.AddWithValue("@MsgText", MessageTxt.Text);
          dbCommand.Parameters.AddWithValue("@MsgId", 0);
          dbCommand.Parameters["@MsgId"].Direction = ParameterDirection.Output;
          dbCommand.ExecuteNonQuery();
          MsgId = Convert.ToInt32(dbCommand.Parameters["@MsgId"].Value);
    
          if (MsgId > 0)
          {
            InfoLBL.Visible = true;
            InfoLBL.Text = "Your message send successfully to " + MsgToDDL.SelectedItem.Text + ". Message id: " + Convert.ToString(MsgId);
          }
          else
          {
            InfoLBL.Visible = true;
            InfoLBL.Text = "Message cannot be sent please contact system administrator";
          }
        }
        catch (Exception ex)
        {
          log(logger.LogSeverity.ERR, "Message cannot be sent, error: " + ex.Message);
          InfoLBL.Visible = true;
          InfoLBL.Text = "Message cannot be sent please contact system administrator";
        }
        finally
        {
          dbConnection.Close();
          dbCommand.Dispose();
        }
        
      }
    }
    ALTER PROCEDURE [dbo].[sp_NewMessageEntry] 
    	-- Add the parameters for the stored procedure here
    	@MsgFrom nchar(20) = NULL, 
    	@MsgTo nchar(20) = NULL,
    	@MsgDate datetime = NULL,
    	@MsgSubject nvarchar(50) = NULL,
    	@MsgText nvarchar(MAX) = NULL,
    	@MsgId int OUTPUT
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
      -- Insert statements for procedure here
    	INSERT INTO messages
    	(
    		MsgFrom,
    		MsgTo,
    		MsgDate,
    		MsgSubject,
    		MsgText,
    		MsgFlag
    	)
    	values
    	(
    		@MsgFrom,
    		@MsgTo,
    		@MsgDate,
    		@MsgSubject,
    		@MsgText,
    		0
    	)
    	
    	SELECT @MsgId = SCOPE_IDENTITY();
    END

    'Message' table where i am storing data.

    CREATE TABLE [dbo].[messages](
    	[messageid] [int] IDENTITY(1,1) NOT NULL,
    	[msgfrom] [nchar](20) NULL,
    	[msgto] [nchar](20) NULL,
    	[msgdate] [datetime] NULL,
    	[msgSubject] [nvarchar](50) NULL,
    	[msgText] [nvarchar](max) NULL,
    	[msgflag] [int] NULL,
     CONSTRAINT [PK_messages] PRIMARY KEY CLUSTERED 
    (
    	[messageid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    It is very simple piece of code. Nothing very complicated.

    Please help me to find the solution.

     

    Thanks

    Wednesday, April 28, 2010 5:12 AM
  • Your code looks correct.

    Are you sure you data being truncated?

    What does following query returns?

    SELECT  DATALENGTH(msgText) As Length , msgText
    FROM messages
    ORDER BY  Length Desc;

    Thursday, April 29, 2010 1:37 AM
  • Hi,

    The message string may be truncated by some other code, please double check your code more carefully, also including the connection string.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, April 29, 2010 8:59 AM
  • hi,

    I'm currently on the idea of ChunSong Feng.. can you please try specifying the "size" of the parameter at (Ado.Net) parameter generation, like

    Dim paramMsgText As SqlParameter = _
                New SqlParameter("@MsgText", _
                   SqlDbType.NVarChar, -1)
            paramMsgText.Direction = ParameterDirection.Input
            command.Parameters.Add(paramMsgText)

    where "-1" explicitely indicates a "MAX" sized value?

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Thursday, April 29, 2010 3:29 PM