question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft commented

building dynamic columns not complete why ?

I work on sql server 2012 i build rows as columns dynamically

but my issue not all rows display as columns on final result separated comma

there are remaining rows and last columns not complete why and how to solve it

to more clear last column must be [Competitor Total Harmonic Distortion]

it display [Competitor To

are this issue of length or what I don't know

can any one help me please

this is my statement generate issue

 DECLARE @result NVARCHAR(MAX) =  
      stuff((( Select  ',['+FeatureName +']' 
     FROM extractreports.dbo.ctegroupfeatur with(nolock) 
      GROUP BY FeatureName,displayorder
      ORDER BY  (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc
                            FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)'))
                      ,1,1,'') 
                      print @result


ddl and insert statement attached with post
87029-ddl-and-insert-statment.txt


and this image show issue exist on last column not surounded with []
and not complete and another columns following not come
so How to solve issue
87101-imgaeshowissue.png


87112-result-file.txt


sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ahmedsalah-1628

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628

There is one limitation that in SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

I checked your query and found that the length of @result was 4456. So the output of print was truncated since it was bigger than 4000 while you declare it as nvarchar.

There are some potential workarounds:

  1. add some logic to test the length of the variable and split into manageable chunks of 8000 or less varchars, and PRINT each one

  2. set 'Results to Text' and use select @result

The second option could be better in your situation since you only would like to debug the complete query.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

PRINT only displays the first 8000 bytes. If you use a SELECT statement, you should get back the entire string.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.