473,573 Members | 3,239 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Server 2K truncating text fields at 255 chars

I'm running SQL Server 2K (sp3a) and when I run the following query (in
query analyzer):

SELECT id, LEN(ForeignWord ) as Length, ForeignWord
FROM Words
WHERE Language ='Thai' and LEN(ForeignWord ) > 300
ORDER BY Length desc

I receive the following results:

id Length ForeignWord
------- ----------- -----------
34756 445 เ&#35...truncated at 255
34839 412 เ&#36...truncated at 255
37613 350 โ&#35...truncated at 255
37808 315 โ&#35...truncated at 255
38140 315 โ&#35...truncated at 255

(The ForeignWord field is defined as varchar(1000))
Note that even though the server says that the lengths are in excess of
255 characters, the results are all truncated at 255 characters.

I read that SQL Server 6.5 and 7.0 had some issues related to native
access versus OleDb or ODBC, but this is Server 2K patched with the
latest updates.

Any idea why my varchar fields are being truncated?

-- Rick

Here is my table definition:

CREATE TABLE [Words] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Language] [varchar] (50) NOT NULL ,
[Module] [int] NOT NULL ,
[Lesson] [int] NOT NULL ,
[EnglishWord] [varchar] (1000) NOT NULL ,
[ForeignWord] [varchar] (1000) NOT NULL ,
[Note] [varchar] (2000) NULL ,
[Military] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Milit ary]
DEFAULT (0),
[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1
_Supplemental] DEFAULT (0),
[SoundFileName] [varchar] (1000) NULL
)

Jul 20 '05 #1
4 13813


Guinness Mann wrote:
I'm running SQL Server 2K (sp3a) and when I run the following query (in
query analyzer):

SELECT id, LEN(ForeignWord ) as Length, ForeignWord
FROM Words
WHERE Language ='Thai' and LEN(ForeignWord ) > 300
ORDER BY Length desc
Try this:

SELECT id, LEN(ForeignWord ) as Length, convert( text, ForeignWord)
FROM Words
WHERE Language ='Thai' and LEN(ForeignWord ) > 300
ORDER BY Length desc


I receive the following results:

id Length ForeignWord
------- ----------- -----------
34756 445 เ&#35...truncated at 255
34839 412 เ&#36...truncated at 255
37613 350 โ&#35...truncated at 255
37808 315 โ&#35...truncated at 255
38140 315 โ&#35...truncated at 255

(The ForeignWord field is defined as varchar(1000))

Note that even though the server says that the lengths are in excess of
255 characters, the results are all truncated at 255 characters.

I read that SQL Server 6.5 and 7.0 had some issues related to native
access versus OleDb or ODBC, but this is Server 2K patched with the
latest updates.

Any idea why my varchar fields are being truncated?

-- Rick

Here is my table definition:

CREATE TABLE [Words] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Language] [varchar] (50) NOT NULL ,
[Module] [int] NOT NULL ,
[Lesson] [int] NOT NULL ,
[EnglishWord] [varchar] (1000) NOT NULL ,
[ForeignWord] [varchar] (1000) NOT NULL ,
[Note] [varchar] (2000) NULL ,
[Military] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Milit ary]
DEFAULT (0),
[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1
_Supplemental] DEFAULT (0),
[SoundFileName] [varchar] (1000) NULL
)


Jul 20 '05 #2
Guinness Mann (GM***@dublin.c om) writes:
I read that SQL Server 6.5 and 7.0 had some issues related to native
access versus OleDb or ODBC, but this is Server 2K patched with the
latest updates.

Any idea why my varchar fields are being truncated?


In Query Analyzer, Tools->Options->Results->Maximum character per column.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
In article <Xn************ **********@127. 0.0.1>, so****@algonet. se
says...
Any idea why my varchar fields are being truncated?


In Query Analyzer, Tools->Options->Results->Maximum character per column.


That's really funny. It absolutely was the solution for my truncation
in ISQL/W.

But the reason I was looking at the data in Query Analyzer was because
another developer, using PHP, was getting truncated results using
whatever data access method PHP users use.

So it was a coincidence that I saw the same symptoms they were seeing.

I wrote a .NET program in C# to retrieve the data and got full,
untruncated data.

I don't know why PHP truncates their data, but as far as I'm concerned,
it's not a SQL Server problem.

-- Rick

Jul 20 '05 #4
Guinness Mann (GM***@dublin.c om) writes:
That's really funny. It absolutely was the solution for my truncation
in ISQL/W.

But the reason I was looking at the data in Query Analyzer was because
another developer, using PHP, was getting truncated results using
whatever data access method PHP users use.


I know very little about PHP. But I've PHP users who had had connection
problems and their error messages indicates that they are using
DB-Library. DB-Library is an abandoned client library, and does not
support varchar data > 255, so if you need that long data with PHP
library, you need to use the text data type.

Now, just like there is more than one way to connect to SQL Server from
Perl, there might be more than one way from PHP.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2922
by: James | last post by:
I am currently working on a PHP based website that needs to be able to draw from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I took a lot of time and care creating a flexible and solid wrapper and am deep into coding. The only problem is a noticed VARCHAR fields being drawn from SQL Server 2000 are being truncated to...
3
2159
by: Fardude | last post by:
I have a few tables in SQL Server 2000 and a small Access 97 that has linked tables used to insert/update/delete into SQL Server 2k tables. Everything works fine except Updating one of these table through Access, I get "Write conflict error" which basicaly says since you have updated this record some other user has updated it. I know for...
1
2880
by: bonnie.tangyn | last post by:
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application...
5
37544
by: VM | last post by:
How can I truncate a string? For example, if I have a string containing "Hello All" but the space reserved for this string is of 4 chars, how can I truncate it to "Hell"? Would I need to create my own method for this or is there an exisiting String method that does it? Thanks
17
11821
by: tommy | last post by:
Hi all, I' m adding strings to some fields in my table via Access. The strings sometimes have trailing spaces and I really need to have it that way, but Access truncates trailing spaces. How can I force Access not to truncate?! Thanx, /Toommy
7
6974
by: h7qvnk7q001 | last post by:
I'm trying to implement a simple server-side form validation (No Javascript). If the user submits a form with errors, I want to redisplay the same form with the errors highlighted. Once the form is correct I need to submit to another page that uses the form data. I first tried making the form submit action= field point to the same file....
0
1290
by: Rob van Dort | last post by:
I would like to have a piece of code (XSL, Java) with which I can truncate strong typed text fields in an XML documents according to length definitions in an XSD. Anybody seen such a piece of code before?
0
3922
by: hellosibba | last post by:
Hi i am trying to use text file to write a value as soon as there is any update/insert into a table. for that i am using a text file and made a linked server relation with it..... and inserting into the needed value in the text file usind linked server insert command..... Till here its working fine.... now with time the text file is growing...
8
8824
code green
by: code green | last post by:
I am using an ODBC connection to a MS Access database. When using a SELECT statement and collecting the data via php ODBC functions, I find that the data from TEXT fields is truncated to 255 characters. The texr fields are up to 1400 chars in length and some contain HTML tags, but the maximum length returned is 255. Any insight appreciated
0
7741
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8026
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6347
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5550
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5252
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3692
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3686
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2163
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
984
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.