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)
http://www.simple-talk.com
Thanks in advance
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
Thanks in advance
Microsoft SQL Server 2008
Last Comment
Shawn Cøady
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
ASKER
This worked perfectly!
ASKER
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.