Data is truncated /SQL query help

Please help me to understand why the data is being truncated. Please run
this code. If you notice, the final @.select does not show all the names
which are in the table. It is cutting off. (even though the length of
the @.Name is declared to varchar(8000)). What I am missing?
Thanks for your help.
create table #Names
(Name sysname)
insert into #Names values('user_audit_log 13-Mar-2006.xls')
insert into #Names values('user_audit_log 14-Mar-2006.xls')
insert into #Names values('user_audit_log 15-Mar-2006.xls')
insert into #Names values('user_audit_log 16-Mar-2006.xls')
insert into #Names values('user_audit_log 17-Mar-2006.xls')
insert into #Names values('user_audit_log 18-Mar-2006.xls')
insert into #Names values('user_audit_log 19-Mar-2006.xls')
insert into #Names values('user_audit_log 20-Mar-2006.xls')
insert into #Names values('user_audit_log 21-Mar-2006.xls')
insert into #Names values('user_audit_log 22-Mar-2006.xls')
declare @.Name varchar(8000)
select @.Name = ''
select @.Name = @.Name + case when len(@.Name) > 0 then ',' else '' end +
[Name]
from Names
select * from #Names
select @.Name --final
*** Sent via Developersdex http://www.examnotes.net ***Test wrote on Thu, 23 Mar 2006 07:32:49 -0800:

> Please help me to understand why the data is being truncated. Please run
> this code. If you notice, the final @.select does not show all the names
> which are in the table. It is cutting off. (even though the length of
> the @.Name is declared to varchar(8000)). What I am missing?
> Thanks for your help.
> create table #Names
> (Name sysname)
> insert into #Names values('user_audit_log 13-Mar-2006.xls')
> insert into #Names values('user_audit_log 14-Mar-2006.xls')
> insert into #Names values('user_audit_log 15-Mar-2006.xls')
> insert into #Names values('user_audit_log 16-Mar-2006.xls')
> insert into #Names values('user_audit_log 17-Mar-2006.xls')
> insert into #Names values('user_audit_log 18-Mar-2006.xls')
> insert into #Names values('user_audit_log 19-Mar-2006.xls')
> insert into #Names values('user_audit_log 20-Mar-2006.xls')
> insert into #Names values('user_audit_log 21-Mar-2006.xls')
> insert into #Names values('user_audit_log 22-Mar-2006.xls')
> declare @.Name varchar(8000)
> select @.Name = ''
> select @.Name = @.Name + case when len(@.Name) > 0 then ',' else '' end +
> [Name]
> from Names
> select * from #Names
> select @.Name --final
> *** Sent via Developersdex http://www.examnotes.net ***
Works fine here. I'll assume you are using Query Analyzer - it's got a
default max length on display. Go to the options and increase it.
Dan|||Where are you testing your code, in Query Analyzer?
See Tools - Options... - Results - Maximum characters per column (Max value
8k)
AMB
"Test Test" wrote:

> Please help me to understand why the data is being truncated. Please run
> this code. If you notice, the final @.select does not show all the names
> which are in the table. It is cutting off. (even though the length of
> the @.Name is declared to varchar(8000)). What I am missing?
> Thanks for your help.
>
> create table #Names
> (Name sysname)
> insert into #Names values('user_audit_log 13-Mar-2006.xls')
> insert into #Names values('user_audit_log 14-Mar-2006.xls')
> insert into #Names values('user_audit_log 15-Mar-2006.xls')
> insert into #Names values('user_audit_log 16-Mar-2006.xls')
> insert into #Names values('user_audit_log 17-Mar-2006.xls')
> insert into #Names values('user_audit_log 18-Mar-2006.xls')
> insert into #Names values('user_audit_log 19-Mar-2006.xls')
> insert into #Names values('user_audit_log 20-Mar-2006.xls')
> insert into #Names values('user_audit_log 21-Mar-2006.xls')
> insert into #Names values('user_audit_log 22-Mar-2006.xls')
>
> declare @.Name varchar(8000)
> select @.Name = ''
> select @.Name = @.Name + case when len(@.Name) > 0 then ',' else '' end +
> [Name]
> from Names
> select * from #Names
> select @.Name --final
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Alejandro. It works!
I am running this code in QA on my workstation. The length was set to
256. I changed it to 8K. But this code is going to run on two others
servers as a job. Do I need to reset the length everytime? Can I do it
though the code?
Thanks for your hgelp!
*** Sent via Developersdex http://www.examnotes.net ***|||> I am running this code in QA on my workstation. The length was set to
> 256. I changed it to 8K. But this code is going to run on two others
> servers as a job. Do I need to reset the length everytime? Can I do it
> though the code?
This has nothing to do with the server or the query... this is just the
default behavior of the client presentation tool you happen to be using. If
you use the same installation of QA, the setting should stick if you run the
same query against any server. Is this report always going to be returned
to QA?|||Aaron,
It would be a stored proc that will run as a job. The proc calls
xp_sendmail for sending multiple files to the recipents. I am building a
string (see my code for @.Name) that will contain multiple file names so
I can pass it to xp_sendmail. It is a sending multiple attachments thru
email task!
As you said, it is only client workstation settings, so in my case, I
should be okay, rite?!
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||> As you said, it is only client workstation settings, so in my case, I
> should be okay, rite?!
Yes. Rite.