Solved

body longer than 8000 in sql sp_send_dbmail?

Posted on 2014-03-03
9
1,331 Views
Last Modified: 2014-03-03
Is there a way to send a may using sql if the body is html format long more than 8000 chars?
I have a store procedure that creates automatically the text and sends to operator.
Quite often the text is longer than 8000chars

Than you
0
Comment
Question by:progold
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39899912
if you declare the variable as varchar(max), it shall work correctly.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39899928
Are you using classic asp?  Which version of sql server?
0
 

Author Comment

by:progold
ID: 39899944
It doesn't work with varchar(max)
I tried and the email has been sent truncated with 8153 chars
0
 

Author Comment

by:progold
ID: 39899959
sql server 2008 R2
I'm not using asp but a sql job with a store procedure
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39900034
The @body parameter for sp_senddb_mail is NVARCHAR(MAX), so there shouldn't be a limitation in the mail sending part itself - which would suggest the problem lies elsewhere.

Can you post the code you are using to generate the body of your message, and send it?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39900041
I agree, it has to work, unless somewhere in the "middle" you use varchar/nvarchar without the MAX specification ...
0
 

Author Comment

by:progold
ID: 39900054
thank you. it's working now
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39900057
Can you expand on what your problem was in the end?

If it was related to a variable somewhere then I think a point split may have been more appropriate option for closing the question.
0
 

Author Comment

by:progold
ID: 39900070
I had a intermediate store procedure which launched sp_send_dbmail.
I deleted it and use directly sp_send_dbmail with the correct nvarchar(max)
Thank you
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now