Limit on string length returned from a query. 2

[tbt102]

I have a query that concatenates the content of 3 columns. The total number of characters with spaces is 564. The query only returns 248 characters with spaces.

Is there a limit on the number of characters that can be returned?

Thanks in advance for any help.

tbt102

 

[Deeba]

Often there is a limit to the characters returned in a column. Try using QA and going into tools - options - results(tab) and changing the "Maximum characters per column" to a larger number. The max is 8192 characters.

If this doesn't make a difference post your query, or error message.

~Deeba~

 

[martynh]

I'd guess that your 3 columns are all declared as fixed length character, rather than varchar. When you concatenate the 3 columns together SQL Server will probably retain the data type as char (or nchar). This will be limited then to the maximum length for a char column.

As an experiment, try casting (CAST or CONVERT function) one of the columns to varchar before the concatenation. This should force the result to come back as varchar and you should get all of your data.

Regards

Martyn Hodgson
CDE Solutions

 

[tbt102]

Marthynh,
The columns have been implemented with datatype varchar 255. Tried the convert anyhow and got the same results.

I posted the sql below.

Thanks in advance for any help.

SELECT OTV.VALUE
+ CASE WHEN OTV_1.SEQUENCE_NUMBER IS NULL THEN '' ELSE OTV_1.VALUE END
+ CASE WHEN OTV_2.SEQUENCE_NUMBER IS NULL THEN '' ELSE OTV_2.VALUE END
+ CASE WHEN OTV_3.SEQUENCE_NUMBER IS NULL THEN '' ELSE OTV_3.VALUE END
+ CASE WHEN OTV_4.SEQUENCE_NUMBER IS NULL THEN '' ELSE OTV_4.VALUE END
+ CASE WHEN OTV_5.SEQUENCE_NUMBER IS NULL THEN '' ELSE OTV_5.VALUE END
FROM OBJECT_TEXT_VALUE OTV
LEFT JOIN OBJECT_TEXT_VALUE OTV_1
ON (OTV_1.OBJECT_ID = OTV.OBJECT_ID
AND OTV_1.SEQUENCE_NUMBER = 1)

LEFT JOIN OBJECT_TEXT_VALUE OTV_2
ON (OTV_2.OBJECT_ID = OTV.OBJECT_ID
AND OTV_2.SEQUENCE_NUMBER = 2)

LEFT JOIN OBJECT_TEXT_VALUE OTV_3
ON (OTV_3.OBJECT_ID = OTV.OBJECT_ID
AND OTV_3.SEQUENCE_NUMBER = 3)

LEFT JOIN OBJECT_TEXT_VALUE OTV_4
ON (OTV_4.OBJECT_ID = OTV.OBJECT_ID
AND OTV_4.SEQUENCE_NUMBER = 4)

LEFT JOIN OBJECT_TEXT_VALUE OTV_5
ON (OTV_5.OBJECT_ID = OTV.OBJECT_ID
AND OTV_5.SEQUENCE_NUMBER = 5)

,OBJECT LIB
,OBJECT DIAG
,OBJECT OBJ_ENT
,ENTITY ENT
,OBJECT TBL
WHERE OTV.SEQUENCE_NUMBER = 0
AND OTV.PROPERTY_ID = 402
.
.
.;

 

[martynh]

I've tried to reproduce your error, and can't do so. I created a table and populated it thus:

create table tab3
( t1 varchar(255)
, t2 varchar(255)
)

insert into tab3 values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890',null);
insert into tab3 values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','abcdefghijklmnopqrstuvwxyz');

By running the query:

select t1
+ case when t2 is null then ''
else t2 end
from tab3

I get the correct data in both rows. My test query was through Query Analyser. So, a couple of questions:

1. Does my simple test replicate your situation?
2. Are you testing through Query Analyser?

It strikes me that maybe you are (either directly or indirectly) allocating storage based on the data returned from the first row and thus are truncating subsequent data. I've seen this sort of problem with badly written ODBC drivers, although I doubt the SQL Server ODBC would have problems with something so simple. I wonder what the metadata says for the row set (either the SQLDA or recordset properties collection depending on your connection mechanism).

As an aside, for this type of conditional test I'd guess that the COALESCE function is more effecient, and possibly more stable!

Regards

Martyn Hodgson
martyn.hodgson@cdesolutions.com

 

[tbt102]

1. Does my simple test replicate your situation?
Yes

2. Are you testing through Query Analyser?
Yes

not familiar with COALESCE function.

 

[tbt102]

1. Does my simple test replicate your situation?
Yes

2. Are you testing through Query Analyser?
Yes

Not familiar with COALESCE function.

I also put together a VB program that executes the query. The query still only returns 248 characters with spaces.