Home Forums SQL Server 2008 SQL Server Newbies SQL 2008 R2 varChar(Max) field not storing all data.. cutting off. RE: SQL 2008 R2 varChar(Max) field not storing all data.. cutting off.

  • April 25, 2017 at 7:17 pm

    #1939859

    spitfire677 - Tuesday, April 25, 2017 3:55 PM

    I recently moved my database from an older version of MS SQL Express to MS SQL Express 2008 R2. I have one field that stores a large amount of text and HTML markup that I later use to include in a web page via Coldfusion. This field is set to datatype varChar(max).
    The data is written to the database with a simple UPDATE query as follows:

    Note the CAST function was just added while trying to sort this out

    UPDATE KeyPostings
    SET
    PostingDate = '#PostingDate#',
    Posting = CAST('#Posting#' AS varChar(Max)),
    Author = '#Author#',
    PostingDescription = '#PostingDescription #',
    MenuPost = '#MenuPost#',
    GATAPost = '#GATAPost#',
    GATAPosted = '#GATAPosted#',
    AuthorUserID = '#AuthorUserID #'
    WHERE PostingID = '#PostingID#'

    I have verified that the entire data is not being stored by running a query in SQL Manager to retreive the Len() of the field "posting." Once maxed out at arond 69988 subsequent saving of additional data (added to the end of the existing data in our form field) does not increase the Len reported.

    All help greatly appreciated!!

    Can you post the code that you're using to add the additional data to the end of the existing data, please.  If it's from a variable or table, please be sure to list the datatype of that variable or the column.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)