appending nvarchar variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

appending nvarchar variable

Author

Topic

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 09:51:27

Hi,

I have a very weird problem. My temp. table hold's 15000 rows and I want to make 1 big comma separated string. I am using this sql

declare @list nvarchar(max)
set @list = ''
select @list = @list + emailaddress + ', ' from #lastTable

But the resulting @list value is just 4000 characters, representing the first 310 rows only!

Hasn't helped a lot even if I force a cast by

select @list = cast(@list as nvarchar(max)) + cast(emailaddress as nvarchar(max)) + ', ' from #lastTable

Totally lost here, please help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 09:59:10

did you try printing out variable value using select @list? what does it give you?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:59:34

How do you know that @list is only 4000 characters?

declare @list nvarchar(max)
set @list = ''
select @list = @list + emailaddress + ', ' from #lastTable

select @list, datalength(@list) / 2

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:01:15

also make sure you've set property maximum characters displayed in SQL Mgmnt studio to more than 4000 for you to view full result

http://msjawahar.wordpress.com/2008/01/25/how-to-change-the-maximum-number-of-character-displayed-in-sql-server-query-analyzer-and-management-studio/

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 10:05:44

Hi,

The @list value prints off a list with commas but its way way less than what it should be (used a function to regenerate the contents into a table splitting on commas and that proved it was missing entries).

the datalength(@list)/2 gives 376647

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:08:42

quote:
Originally posted by actuary

Hi,

The @list value prints off a list with commas but its way way less than what it should be (used a function to regenerate the contents into a table splitting on commas and that proved it was missing entries).

the datalength(@list)/2 gives 376647

Thanks!

are you viewing in grid or text mode? try grid mode

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 10:11:51

I am in grid only mode.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:15:44

what does this give you?


select count(emailaddress) from #lastTable

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 10:16:31

the count from the #lastTable gives me 15718 row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:18:41

what does this return you

EXEC sp_dbcmptlevel 'your database name here'

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 10:21:21

The current compatibility level is 90.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:23:54

are you just using @list variable directly in select or are you appending this to someother data and selecting?

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 10:30:37

select @list = cast(@list as nvarchar(max)) + cast(emailaddress + ', ' as nvarchar(max)) from #lastTable

its just appending to itself reading from the table and adding a comma after each entry.

actuary
Starting Member

23 Posts

Posted - 2009-01-08 : 11:29:55

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283368

funny, this doesn't work :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources