a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

what is the error message|||

I do not see error, in the query analyzer, I set “Results in text” and run the query, I see first 8 records and (1 row(s) affected) message, it should show at least 50 rows. Is this a query analyzer problem?

|||

how about this

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName as nyfield From MyTable

|||

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

|||

i think QA is displaying it in a very long line

have this a try

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

print @.ColList

|||

JIM.H. wrote:

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

tried to simulate you can only make it until 4000

i think you should make use of cursor

|||

If you are using SQL 2005 please check the following:

Tools -> Options -> Query Results -> Results to Text Maximum number of characters displayed in each column (the default is 256)

Tools -> Options -> Query Results -> Results to Grid Maximum Characters Received Non XML data (the default is 65536)

In SQL 2000 in QA

Tools -> Options -> Results ->Maximum number of characters per column (the default is 256)

You may need to increase these numbers

|||

i tried this one in northwind

use northwind

Declare @.ColList char(8000)
Declare @.CrLf varchar(2)
Select @.CrLf=Char(13) + Char(10)
Select @.coLlist= COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + RTRIM(LTRIM(customerid)) From orders

print @.coLlist
select len(@.collist) as txtlength -<- check this out
select datalength(@.collist) as datalenght <-- and this is

here's the result

4000

8000

|||this worked. Thanks.|||there's a limit of byte per row that can be returned, inserted or updated...it's 8096 if i remember correctly...