May 23, 2004 at 12:53 pm
What data type supports 2000 characters or more? I have tried varchar and nvarchar with the length set to 8000 but they max out at 1023 characters. I also tried text and ntext but I can not increase the length over 16 in Enterprise Manager.
I'm using SQL Server 2000. I'm sure this is a common question but I'm stuck just the same.
Thanks in adavace for your help.
May 23, 2004 at 10:32 pm
In SQL 2000, varchar will support a max of 8000 chars, nvarchar will support a max of 4000 chars, text supports up to 2gb of data (and, as you noticed in EM the length is displayed as 16)
When does it max out?
If it "maxes out" in QueryAnalyser, you may need to select Tools/Options from the menu and set the "maximum characters per column" on the Results tab to a larger number. I think this value is also used by Debugger window.
May 24, 2004 at 3:04 am
Don't forget that although individual varchar or nvarchar fields can have a length of 8000, there is also a limit of 8000 characters total on the row. If the total of all the other fields in the table exceed 8000-1023=6977, then there will only be 1023 left for your new varchar.
Could this be the reason why they "max out at 1023 characters"?
Note that the 8000 limit and the rest of my calculation ignores the system overhead bytes, (just to keep it simple)
Tony
May 24, 2004 at 4:40 am
It sounds like you're trying to enter/update the data using Enterprise Manager. In that case the maximum you can enter is 1023 characters, even if you have defined a varchar field up to 8000 characters.
You'll need to use an alternative tool (eg. Query Analyzer) to enter the longer data values.
Cheers,
- Mark
May 24, 2004 at 7:50 am
Great! That's it. I was entering the data with Enterprise Manager. I tried it with Query Analyzer and it worked as expected.
Thanks for you help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply