-1

How can I convert a simple select statement into SQL SERVER AGENT JOB for an auto Email to an operator. e.g. USE [DYNAMICS] select * from ACTIVITY I daily need this ACTIVITY table contents at 10:00am via Email. How I'll do it as auto Email from SQL SERVER AGENT? Help required.

adeel
  • 17
  • 8
  • [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/2029983) – Thom A Jul 24 '20 at 10:50
  • What were wrong with the examples in the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15#examples)? – Thom A Jul 24 '20 at 10:51

1 Answers1

0

SQL SERVER have a function sp_send_dbmail that will help. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15

Configure your server:

execute sp_configure 'Show Advanced Options', 1
reconfigure
execute sp_configure 'Database Mail XPs', 1
reconfigure

Configure your account:

execute msdb.dbo.sysmail_add_account_sp
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@enable_ssl = 1,
@account_name = 'mail_account', 
@display_name = 'SQL SERVER',
@email_address = 'your@email.com',
@username = 'your@login',
@password = '*** password ***'

execute msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'mail_profile',
    @description = 'Mail notification by SQL.'

execute msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'mail_profile',
    @account_name = 'mail_account',
    @sequence_number = 1

Send mail

execute msdb.dbo.sp_send_dbmail 
    @profile_name = 'MAIL NAME',
    @recipients = 'mailto@mailto.com',
    @subject = 'subject',
    @body = 'your message'

Thats all!

Kennedy Carvalho
  • 1
  • 2
  • Email is configured and it is sending me Emails when a task is completed or failure. I need to a execute a SELECT statement and then I need the results/report of that select statement on my Email. How I will do that? How I will get that report in .txt or some other format? – adeel Jul 24 '20 at 11:23
  • There's an explanation in that link https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15#c-sending-an-html-e-mail-message – Kennedy Carvalho Jul 24 '20 at 11:31