String size exceeds than maximum allowed

Question

String size exceeds than maximum allowed

mageshh11

Old Hand

Points: 377
More actions
July 8, 2011 at 1:08 am

#243946

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tommyh SSCertifiable Points: 6302 More actions · Answer 1

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

mageshh11 Old Hand Points: 377 More actions · Answer 2

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.

winash SSChampion Points: 11446 More actions · Answer 3

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

tommyh SSCertifiable Points: 6302 More actions · Answer 4

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

mageshh11 Old Hand Points: 377 More actions · Answer 5

mageshh11

Old Hand

Points: 377

July 8, 2011 at 4:07 am

#1350224

Thanks for the clarification.