none
Length limit on @body in sp_send_dbmail... is there one?

    Question

  • Hi,

    Probably a dumb question but hopefully some one will humor me.  Its my understanding the @body message body is an nvarchar(max) and as such it should allow for 4000 characters.  When I execute an sp_send_dbmail  and I paste in a 30k character string for the @body regardless of whether the @body_format is set to text or html, it sends it through and doesn't truncate at all.  I have need for an email message that could exceed the 4k limit periodically but am a little nervous relying on it when it seems to behave counter to its published limitations.

    Any ideas as to why this would be?  Be gentle, I am a newbie here!

    Thanks,

    Matt

    Thursday, November 29, 2007 10:32 PM

Answers

  •  mathias63 wrote:

    Its my understanding the @body message body is an nvarchar(max) and as such it should allow for 4000 characters. 

     

    Thankfully, this is a simple misunderstanding. 

     

    Nchar and nvarchar (Transact-SQL):

    http://msdn2.microsoft.com/en-us/library/ms186939.aspx

    Excerpt:

    "Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying."

     

     mathias63 wrote:

    Be gentle, I am a newbie here!

    Welcome to the SQL Community Smile

     

    Paul A. Mestemaker II

    Program Manager

    Microsoft SQL Server

    http://blogs.msdn.com/sqlrem/

    Friday, November 30, 2007 12:10 AM

All replies

  •  mathias63 wrote:

    Its my understanding the @body message body is an nvarchar(max) and as such it should allow for 4000 characters. 

     

    Thankfully, this is a simple misunderstanding. 

     

    Nchar and nvarchar (Transact-SQL):

    http://msdn2.microsoft.com/en-us/library/ms186939.aspx

    Excerpt:

    "Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying."

     

     mathias63 wrote:

    Be gentle, I am a newbie here!

    Welcome to the SQL Community Smile

     

    Paul A. Mestemaker II

    Program Manager

    Microsoft SQL Server

    http://blogs.msdn.com/sqlrem/

    Friday, November 30, 2007 12:10 AM
  • That makes sense! Thanks for the quick response.

     

    Friday, November 30, 2007 12:21 AM