none
Truncation in varchar(max)

    Question

  • Hi There ,

    I am facing issue that when i try to insert very long records into a table(in MS SQL Server 2008) its getting truncated.

    This is where i am trying to insert from my application

     File.AppendAllText("D:\\logs\\query.txt", commandText + "\n"); //printing the full command text into a txt file

     using (Log.Profile("LIService.getCUBEReport", commandText)) //inserting only a part of command text into the profile db's table
    {

    ..........

    }

    Please do help me out with this.

    Thanks,

    Ishwarya

    Tuesday, January 17, 2012 8:45 AM

Answers

  • Hi,

    Try to use replicate function:

    DECLARE @V VARCHAR(MAX) = '*'
    SET @V = REPLICATE(@V, 8051)
    SELECT DATALENGTH(@V)
    

    Check the max length of data you are going to insert and change parameter in replicate as per that.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Marked as answer by IshNair Friday, January 20, 2012 7:53 AM
    Tuesday, January 17, 2012 10:26 AM

All replies

  • Hi,

    Try to use replicate function:

    DECLARE @V VARCHAR(MAX) = '*'
    SET @V = REPLICATE(@V, 8051)
    SELECT DATALENGTH(@V)
    

    Check the max length of data you are going to insert and change parameter in replicate as per that.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Marked as answer by IshNair Friday, January 20, 2012 7:53 AM
    Tuesday, January 17, 2012 10:26 AM
  • No this is not working, i tried this

    DECLARE @V VARCHAR(MAX) = '*'
    SET @V = REPLICATE(@V,2147483649)
    SELECT DATALENGTH(@V)

    But then to the data is getting inserted with truncation.

     

    Tuesday, January 17, 2012 12:17 PM
  • No this is not working, i tried this

    DECLARE @V VARCHAR(MAX) = '*'
    SET @V = REPLICATE(@V,2147483649)
    SELECT DATALENGTH(@V)

    But then to the data is getting inserted with truncation.

     

    Hi IshNair,

    The data type of the target column is VARCHAR(MAX), right?

    Do you get any error message? Could you please show us the code snippets of the parameter definition and INSERT?

    How long are the records? The maximum storage size of VARCHAR(MAX) is 2^31-1 bytes and the size is the actual length of data entered + 2 bytes.

    Do you specify the parameter size in .NET? If yes, please increase the size or set -1.

    Reference:

    Modifying Large-Value (max) Data in ADO.NET
    http://msdn.microsoft.com/en-us/library/bb399384.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Thursday, January 19, 2012 5:56 AM
  • Hi all,

    Thanks all for your support. Actually i made mistake in logging part which actually made the queries to get truncated and saved into the table.

    like this
          public DatabaseParam Param(string name, string value, int size)
            {
                if (String.IsNullOrEmpty(value) || value.Length <= size)
                    return new DatabaseParam(name, value);
                else
                    return new DatabaseParam(name, value.Substring(0, size));
            }       
    And now its resolved. Sorry for the inconvenience.

    thanks again,

    Aish

    Friday, January 20, 2012 7:56 AM
  • Hi!

    I was stuck in the same issue BUT here i was sending the actual data(without truncate) into the database and was unable to retrieve back it completely. So I came up with this idea. Sharing it here if it may help to others:

    http://shahanayyub.wordpress.com/2012/04/22/retrieve-more-than-65535-characters-from-sql-columns-11/


    Sunday, April 22, 2012 10:09 PM