how to send result set of a stored procedure as an automatic email using sql server 2005

Question

Hi all i have a stored procedure which gives the today's payment details. so every day at 9pm i want to send that result set of that procedure as an email to my email account automatically. how can we do this with sql server 2005 or if possible console application by using .net. Please help me..........

DiasparkDotNet DiasparkDotNet · Answer

You can configure Database Mail feature of Sql server and use it for sending the mails directly from stored procedures. The feature is just a replacement of SqlMail feature in previous versions. For using the feature you need to : 1. Create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. 2. After the Account and the Profile are created successfully we need to configure the Database Mail. To configure it we need to enable the Database Mail XPs parameter through the sp_configure stored procedure as shown here: sp_CONFIGURE 'show advanced'  1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs'  1 GO RECONFIGURE GO 3. We need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below: USE msdb GO EXEC sp_send_dbmail @profile_name = 'YourProfile (as created in step 1)'  @recipients = 'test@Example.com'  @subject = 'Test message'  @body = ' >' and you are done.