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