SQL Server 2K truncating text fields at 255 chars

[Guinness Mann]

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
)

[Joseph Weinstein]

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
)

[Erland Sommarskog]

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

[Guinness Mann]

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

[Erland Sommarskog]

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