We help IT Professionals succeed at work.

Substitute Path\FileName for @body in sp_send_dbmail

759 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

DB Expert/Architect
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Shawn CøadyTechonology and Information Systems

Author

Commented:
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.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
You can also use some CLR and plow ;)
Use the SQL Server CLR to Read and Write Text Files
Shawn CøadyTechonology and Information Systems

Author

Commented:
This worked perfectly!
Shawn CøadyTechonology and Information Systems

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.