Avatar of Shawn Cøady
Shawn Cøady
Flag for United States of America asked on

Substitute Path\FileName for @body in sp_send_dbmail

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)

Thanks in advance
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Shawn Cøady

8/22/2022 - Mon

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shawn Cøady

Awesome... this looks promising.  Similar to what I was trying to assemble using Phil Factor's function.  Your approach seems more efficient.  I'll give it a go and add comments.

You can also use some CLR and plow ;)
Use the SQL Server CLR to Read and Write Text Files
Shawn Cøady

This worked perfectly!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Shawn Cøady

I created a wrapper sproc that declared all the xp_smtp_sendmail vars and mapped them as required to the db_send sproc... coupled with the usp_readfile Daniel_PL whipped up.  Thanks again... very helpful.