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.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2009-02-24 : 05:27:38
|
| Hi Pals,Need some help.I have big sql which is more than 4000 characters.Basically the sql is formed by UNION operator.For that reason i have declared the datatype as VARCHAR(MAX).and then tried to assign the value to a variable.and when i am trying to print the SQL using PRINT / SELECT , the sql is getting truncated.What i have done is instead of printing i have used SELECT LEN('SELECT ..........'). Then i am getting output as 8000 chars which can easily be accomodated by VARCHAR(MAX) datatype (i.e 2^31 chars.)Why is the sql is getting truncated? What could be the problem?Any Thoughts?Any suggestions will be greatly appreciated.Thanks in Advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Dancer
Starting Member
2 Posts |
Posted - 2009-02-24 : 10:29:56
|
Hi - I'm not sure if this is what it is but I had a similar problem a while ago and resolved it this way.Right click on the Query Window and select "Query Options". Expand "Results" and highlight "Text". Change the "Maximum number of characters displayed in each column:" setting to suit, otherwise a maximum of 256 characters will only be displayed in the results window with select / print statements.Good luck! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 10:36:27
|
quote:
Originally posted by Dancer
Hi - I'm not sure if this is what it is but I had a similar problem a while ago and resolved it this way.Right click on the Query Window and select "Query Options". Expand "Results" and highlight "Text". Change the "Maximum number of characters displayed in each column:" setting to suit, otherwise a maximum of 256 characters will only be displayed in the results window with select / print statements.Good luck!
yup..thats exactly what i was also telling. i also experienced it once |
|
|
|
Raibeart
Starting Member
8 Posts |
Posted - 2009-02-24 : 11:14:42
|
| I think the problem is that dynamic sql has to use nvarchar and has a max of 4000 characters, so you will have to break it up into segments and use EXEC (@Sql1 + @Sql2 + @Sql3) and have as many segments as you need to break it into. I have some that are 9 levels without the dynamic where clause and order by clause. |
|
|
|
net205
Starting Member
3 Posts |
Posted - 2009-02-24 : 13:03:51
|
| to do like Dancer and RaibeartSuch as:DECLARE @chars VARCHAR(MAX)SET @chars = REPLICATE('3', 8000)SET @chars = @chars + 'abcdefghi'WHILE LEN(@chars)>8000BEGIN PRINT SUBSTRING(@chars, 1, 8000) SET @chars = SUBSTRING(@chars, 8001, LEN(@chars)-8000) ENDPRINT SUBSTRING(@chars, 1, 8000) |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2009-02-25 : 00:09:57
|
| Thanks so much! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-25 : 09:50:14
|
| welcome |
|
|
|
|
|
|
|
|
SELECT string truncation problem!