Send mail from stored Procedure.

db , sql server India
  • 12 years ago
    Hi, From a stored procedure, I need to query the database say , select col1,col2,col3,col4 from tbl1 where col5=condition. and then send these resultant records in a table format as a mail . Can some one help me in doing this..?? thanks, KOTI.
  • 12 years ago
    Check it out: [Link Text](http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure)
  • 12 years ago
    I am trying to do the same thing. The above link did not help much because it was a sp that sends the email. I want to know how to call the results of a sp and place them in my email. Can anyone point me in the right direction?
  • 12 years ago
    Check it out: using System; using System.IO; using System.Data.SqlClient; using System.Text; using System.Web.UI; namespace WebApplication1 { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string mailBody = null; using (SqlConnection con = new SqlConnection("connection string")) { using (SqlCommand cmd = new SqlCommand("stored procedure", con)) { cmd.CommandType = CommandType.StoredProcedure; con.Open(); SqlDataReader sdr = cmd.ExecuteReader(); try { mailBody = GenerateHtmlTable(sdr); } finally { if (sdr != null) sdr.Close(); } } } Response.Write(mailBody); } private string GenerateHtmlTable(SqlDataReader dataReader) { StringBuilder strBuilder = new StringBuilder(); using (StringWriter strWriter = new StringWriter(strBuilder)) { using (HtmlTextWriter htmWriter = new HtmlTextWriter(strWriter)) { htmWriter.AddAttribute(HtmlTextWriterAttribute.Border, "1px"); htmWriter.AddAttribute(HtmlTextWriterAttribute.Bordercolor, "Black"); htmWriter.RenderBeginTag(HtmlTextWriterTag.Table); while (dataReader.Read()) { htmWriter.RenderBeginTag(HtmlTextWriterTag.Tr); for (int i = 0; i < dataReader.FieldCount; i++) { htmWriter.RenderBeginTag(HtmlTextWriterTag.Td); htmWriter.Write(dataReader[i].ToString()); htmWriter.RenderEndTag(); } htmWriter.RenderEndTag(); } htmWriter.RenderEndTag(); } } return strBuilder.ToString(); } } } Hope this helps

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Linux is only free if your time has no value” - Jamie Zawinski