Query a NVARCHAR(MAX) column in the SELECT statement

_{Thursday, April 21, 2011 1:14 AM}

My SQL 2005 table contains a NVARCHAR(MAX) column named Msg.

The data in the Msg column contains more than 4000 chars, and when I SELECT Msg FROM MyTable WHERE ID=1;

The Msg return is truncated and cannot return the complete data in the column.

So how can I select the complete data in the Msg column from MyTable???

xlinkch

_{Thursday, April 21, 2011 7:08 AM ✅Answered}

Just check the MaxLength property of your textbox1 control. Hope, its not set to 4000. Also, please run the query in SSMS and check the length of the output by using the below tsql -

Select LEN(Msg),Msg from MyTable where ID = 1

this will give you an idea if all the characters are being returned or not.

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

_{Thursday, April 21, 2011 2:08 AM}

Where are you sending the query from?

If this is in SSMS, then check " tools - options - query results - results to Grid / Text - maximum number of characters ...".

AMB

Some guidelines for posting questions...

_{Thursday, April 21, 2011 2:27 AM}

All of the characters are actually there but SSMS simply won't display all of them. You can, however copy the value from SSMS and paste into an external editor like NotePad or WordPad and you should see see the whole string.Jason Long

_{Thursday, April 21, 2011 5:37 AM}

I try to query the Msg field from the below code in C#, to display the full complete data in Msg column.

But the data is still truncated, so how can I query out the complete data to display on my textBox1???

            using (SqlConnection conn = new SqlConnection("Persist Security Info=False;User ID=myID;Initial Catalog=MySQL2005;Data Source=Demo"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Select Msg from MyTable where ID = 1";
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                        textBox1.Text = (string)dr.GetValue(dr.GetOrdinal("Msg")); ;

                    dr.Close();
                }
            }

xlinkch

_{Thursday, April 21, 2011 5:45 AM}

Maximum chars accepted in nvarchar(max) is 4000 only, instead use varchar(max).

Amit Govil(amit.govil@hotmail.com)