July 8, 2011 at 1:08 am
Hi,
Using the sql server 2005 db mail provision to send mail.
In this building the body text dynamically (looping through cursor to build string) and then assigning it the @body element of sp_send_dbmail.
Using varchar(max) to assign the body text.
Problem is that my body text exceeds the maximum character of 8000 (which is allowed in varchar(max)).
Not able to use text here, because it shows text can't be used as a local variable.
Is there any other way to assign string of more than 8000 characters.
Please help.
July 8, 2011 at 1:27 am
mageshh11 (7/8/2011)
Hi,Using the sql server 2005 db mail provision to send mail.
In this building the body text dynamically (looping through cursor to build string) and then assigning it the @body element of sp_send_dbmail.
Using varchar(max) to assign the body text.
Problem is that my body text exceeds the maximum character of 8000 (which is allowed in varchar(max)).
Not able to use text here, because it shows text can't be used as a local variable.
Is there any other way to assign string of more than 8000 characters.
Please help.
The limit of varchar(max) isnt 8K its 2G. Maybe your using another variable to store the text before it gets added to the varchar(max) variable. And that variable isnt varchar(max) and hence causes the problem.
I would check that all strings being used are varchar(max) (or convert them to that).
/T
July 8, 2011 at 1:43 am
Thanks for the quick reply.
But all the variables declared in the SP are varchar(max)
Below is the code like which i m using. This may help you to understand my problem
Cursor which has more than 50 rows and 10 columns each
For each row
Set @Summary = @Summary + COL1 + COL2 + COL3 + COL4 + COL5 + COL6 + COL7 + COL8 + COL9 + COL10
Now the actual len(@Summary) is 11821
But when i say print @Summary, it is printing only 8000 characters. Remaining is terminated.
July 8, 2011 at 1:55 am
That's a limitation of the PRINT command - from BOL:
A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated
Use this script http://www.sqlservercentral.com/scripts/Print/63240/ if you want to see >8000 characters printed out
July 8, 2011 at 1:56 am
mageshh11 (7/8/2011)
Thanks for the quick reply.But all the variables declared in the SP are varchar(max)
Below is the code like which i m using. This may help you to understand my problem
Cursor which has more than 50 rows and 10 columns each
For each row
Set @Summary = @Summary + COL1 + COL2 + COL3 + COL4 + COL5 + COL6 + COL7 + COL8 + COL9 + COL10
Now the actual len(@Summary) is 11821
But when i say print @Summary, it is printing only 8000 characters. Remaining is terminated.
The PRINT command can only print 8000. See http://msdn.microsoft.com/en-us/library/ms176047.aspx. (and its that in all version)
So its a display problem. Print the length of the string and you should see that all the data is there.
Damn beat by winash by 30s 🙂
/T
July 8, 2011 at 4:07 am
Thanks for the clarification.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply