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 @.ColListworks fine on my end.
JIM.H. wrote:
> 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|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:
>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
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
--
Hugo Kornelis, SQL Server MVP

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...

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

Did you have any SET ROWCOUNT prior to running this SQL> I ran it on my machine and it worked fine for me.|||

If you are using Query Analyzer to execute the query, please go to Tools menu->Options->switch to Results tab->set the 'Maximum characters per column' to max allowed value 8192, then try again.

If you're using Management Studio and you have set to return result as text, please go to Tools->Options->Query Results->SQL Server->Results to Text->set the 'Maximum number of characters displayed in each column' to 8192

|||Thats right. I had changed mine to 1200 sometime back.

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 @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is bi
g
> 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|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:

>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
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
[url]http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html[/u
rl]
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
Hugo Kornelis, SQL Server MVP