Data is truncated /SQL query help

23 Mar 2006 3:32 PM

Test Test

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.developersdex.com ***

23 Mar 2006 3:45 PM

Daniel Crichton

Test wrote on Thu, 23 Mar 2006 07:32:49 -0800:

Show quote

> 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.developersdex.com ***

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

23 Mar 2006 3:50 PM

Alejandro Mesa

Where are you testing your code, in Query Analyzer?

See Tools - Options... - Results - Maximum characters per column (Max value
8k)

AMB

Show quote

"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.developersdex.com ***
>

23 Mar 2006 4:05 PM

Test Test

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.developersdex.com ***

23 Mar 2006 4:53 PM

Aaron Bertrand [SQL Server MVP]

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

23 Mar 2006 5:14 PM

Test Test

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.developersdex.com ***

23 Mar 2006 5:57 PM

Aaron Bertrand [SQL Server MVP]

> As you said, it is only client workstation settings, so in my case, I
> should be okay, rite?!

Yes. Rite.