Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 1st, 2007, 08:48 AM
Registered User
 
Join Date: Feb 2007
Location: cincinnati, ohio, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Is maxium size of varchar data type is 8000?

Hi All,

I am the new member of this forum. Please help me out with the following query.

I have created a log table with 6 columns of different data type and size. out of these, two columns are with size 3000 each i.e. varchar(3000). I have checked with total size of table and its not even exceeding 8000. Everything was fine and the table was created successfully with out any error. Even while inserting data into that table its not throwing any error.

But to my surprise when I see the data in a table, the column size with varchar(3000) contains only 256 character data. that means the data of full size is not getting loaded. Its truncating.

Could any one please clarify why the size of 3000 is not holding 3000 char and why it holds only 256 char?

I am using sql server 2000 sp4 version

please let me know if any one require further information.

Hoping for a quick reply

Thanks

kawrat


kawrat
Reply With Quote
  #2 (permalink)  
Old February 1st, 2007, 03:10 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In Query Analyzer, from the menu go to Tools, Options, Results and increase the "Maximum characters per column" setting.
Reply With Quote
  #3 (permalink)  
Old February 1st, 2007, 03:15 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

....You say: Even while inserting data into that table its not throwing any error. This cannot be correct. If you try to insert data into a column and your data is longer then the column will allow, SQL will throw a "String or Binary data will be truncated..." error.

Have you done a SELECT query on your table to look at the data?

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Reply With Quote
  #4 (permalink)  
Old February 1st, 2007, 03:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Make certain that you are dealing with varchar and not nvarchar which has a limit of 4000 characters.

Rand
Reply With Quote
  #5 (permalink)  
Old February 2nd, 2007, 04:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes you need to change the config of your Results Window from the query analyser.
In such cases you can also do a
Code:
SELECT Len(Column_Name) FROM Table_Name
that would have cleared your doubt.

cheers.

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #6 (permalink)  
Old February 2nd, 2007, 06:44 AM
Registered User
 
Join Date: Feb 2007
Location: cincinnati, ohio, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks All. Appreciate for a quick reply.

Yes, configuration was a big issue. Thanks a lot this worked out.

But now it raises another query.

Instead of changing the configuration settings (from 256 – to 3000) is there any alternate option to read the data from the table via code?

Say we have a default option set to 256 maximum column char. Now if I want to read (SELECT) a data from a table I can read only 256 chars of column size 3000. Is there any possibility to read complete data (all 3000 chars) with out changing default settings?

Thanks in Advance


kawrat
Reply With Quote
  #7 (permalink)  
Old February 2nd, 2007, 02:24 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Not from Query Analyzer.

Just an FYI, I change the default from 256 to 8000. Query results in the grid mode will automatically size correctly... and I hardly ever used the text mode although there are occasions.

--Jeff Moden
Reply With Quote
  #8 (permalink)  
Old February 5th, 2007, 04:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

kawrat,

Did you try that with a PRINT statement on Query analyser? It should show you all that in TEXT mode.

Cheers

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SSIS - Column Mappings Default Data Type and Size aehtra SQL Server 2005 0 April 25th, 2008 03:53 PM
how to insert varchar data in numeric column dhirajDac Classic ASP Components 3 March 31st, 2008 07:23 AM
Error converting data type varchar to int. - SQL baysaa SQL Server ASP 1 March 17th, 2008 12:56 AM
Data type change from int to Varchar getxyz SQL Server 2000 1 April 29th, 2005 10:59 AM
Error converting data type varchar to float. Trojan_uk SQL Server ASP 5 April 27th, 2004 03:56 AM



All times are GMT -4. The time now is 05:06 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.