troubleshooting Question

Substitute Path\FileName for @body in sp_send_dbmail

Avatar of Shawn Cøady
Shawn CøadyFlag for United States of America asked on
Microsoft SQL Server 2008
5 Comments1 Solution762 ViewsLast Modified:
Trying to migrate some databases from SQL 2000 and SQL 2005 to new SQL 2008 R2 server.  I have used the xp_smtp_sendmail stored procedure (thanks to the developers at SQLDev.net) -- but that sproc was meant for SQL 7 and SQL 2000.  I got it to work on SQL 2005 (32-bit)... but everything I've read indicates I must move on to using Database Mail and sp_send_dbmail.

I've got that setup an working.  Modifying my application to use the new sproc isn't that much of a problem... however... I make liberal use of the @messagefile parm on xp_smtp_sendmail.  This allows me to use the contents of a text/html file as the body of my email.  This is important/critical because I have a number of jobs/reports that essentially spawn HTML files... which are then sent.  It's not possible to create the @body in-line.  I've searched around and can't find anything that matches what I do.  There was another post that was asking the same question... but the expert who answered didn't know much about the xp_smpt_sendmail sproc... and the poster gave up.

So... I'm sort of assuming right off the bat that I can't assign a file name to @body... but maybe there's a way to read in the contents of an HTML file into a SQL variable?

I'm looking at the blog post from simple-talk.com... which refers to using OLE Automation (which is new ground for me)
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

Thanks in advance
ASKER CERTIFIED SOLUTION
Daniel_PL
DB Expert/Architect
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros