469,291 Members | 1,765 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

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_Military]
DEFAULT (0),
[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1
_Supplemental] DEFAULT (0),
[SoundFileName] [varchar] (1000) NULL
)

Jul 20 '05 #1
4 13507


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_Military]
DEFAULT (0),
[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1
_Supplemental] DEFAULT (0),
[SoundFileName] [varchar] (1000) NULL
)


Jul 20 '05 #2
Guinness Mann (GM***@dublin.com) 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.com) 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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by VM | last post: by
7 posts views Thread by h7qvnk7q001 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.