Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 1st, 2007, 08:48 AM
Registered User
 
Join Date: Feb 2007
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
 
Old February 1st, 2007, 03:10 PM
Registered User
 
Join Date: Feb 2007
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.
 
Old February 1st, 2007, 03:15 PM
Wrox Author
 
Join Date: Oct 2005
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^^
================================================== =========
 
Old February 1st, 2007, 03:36 PM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old February 2nd, 2007, 04:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
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
 
Old February 2nd, 2007, 06:44 AM
Registered User
 
Join Date: Feb 2007
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
 
Old February 2nd, 2007, 02:24 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
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
 
Old February 5th, 2007, 04:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
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





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 02:53 PM
how to insert varchar data in numeric column dhirajDac Classic ASP Components 3 March 31st, 2008 06:23 AM
Error converting data type varchar to int. - SQL baysaa SQL Server ASP 1 March 16th, 2008 11:56 PM
Data type change from int to Varchar getxyz SQL Server 2000 1 April 29th, 2005 09:59 AM
Error converting data type varchar to float. Trojan_uk SQL Server ASP 5 April 27th, 2004 02:56 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.