varchar(max) size limit

35 pts.
Tags:
SQL Server
VARCHAR
I have defined a table field with datatype of varchar(max) but it only stores upto 8k characters, I thought (max) upped this limit to 2g character. How can I get it to store more than 8k characters?



Software/Hardware used:
Windows NT/2000

Answer Wiki

Thanks. We'll let you know when a new response is added.

varchar(max) stores up to 2 Gigs of data per row per column. The SQL Server Management Studio will only show 8k by default. When you look at the value in your application it will show all the data (unless the application is designed to only show 8k characters).

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gent01
    Depends on what database version you are using: SQL Server 2000 can only store up to 8000 characters in a VARCHAR field. I don't believe it supports the MAX option. SQL Server 2005, 2008 still only supports 8000 characters for VARCHAR, but thet also adds the MAX option which will allow you to store 2.1M.
    1,870 pointsBadges:
    report
  • Niranjan56
    I have SQL Server 2005 and I have defined the table field to be varchar(max) but it's not accepting anything more than 8000.
    35 pointsBadges:
    report
  • Niranjan56
    ok - so 2 Gb of data is 2^31 characters, way WAY over 8000 characters - right? so why do I get "[Microsoft][ODBC SQL Server Driver]String data, right truncation" error when I try to insert any thing more than 8000 characters?
    35 pointsBadges:
    report
  • carlosdl
    Well, you didn't mention that you were getting an error, nor did you mention that you were using ODBC. I think the ODBC driver could be causing the error. Are there some other details you might want to provide ??
    69,160 pointsBadges:
    report
  • Niranjan56
    good point Carlos - and apologies to everyone. here is what my ODBC configuration looks like: Microsoft SQL Server ODBC Driver Version 03.85.1117 Data Source Name: LMS Dev Data Source Description: Server: WS2K3032SQL Database: (Default) Language: (Default) Translate Character Data: Yes Log Long Running Queries: No Log Driver Statistics: No Use Integrated Security: Yes Use Regional Settings: No Prepared Statements Option: Drop temporary procedures on disconnect Use Failover Server: No Use ANSI Quoted Identifiers: Yes Use ANSI Null, Paddings and Warnings: Yes Data Encryption: No The SQL Server is running on some Intel machine, VB ver is 6.5,
    35 pointsBadges:
    report
  • msi77
    Try to use SS Native Client as ODBC driver.
    1,660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following