One of the greatest Database Services of the SQL Server is Database Mail, its allow users to send mail using predefined procedures.
To send mail from SQL Server first, you need to configure the Database Mail (Mail Profile).
So, What is Database Mail or Profile ?
To send any mail we require some details like From Email and Reply Email, but internally to send mail we need server name which is nothing but domain name (for example gmail.com, live.com, Hotmail and so on..) and port number(mostly its 25 but it may change according to the server name). Why are we discussing all these details, because we fill all these details at the time of Data Mail or Profile Configuration.
Without wasting any time lets dive in to how to configure Database Mail:
1. Open SQL Server Management Studio, connect to the server on which you want to configure the Database Mail. If you don't have any servers created in your SQL Server Management Studio, try to connect local server which is default for all.
2. After connecting to the local, expand Management and right click on Database Mail you will get to see the below similar screen.
2. Now, Click on the Configuration Database Mail Click Next >. This will open Database Mail Configuration Wizard.
3. You will see "Select Configuration Task Window", based upon your requirement you need to choose the options as of now you are creating new mail profile select “Set up Database Mail by performing the following tasks: and Click Next.
4. Now, New Profile Window opens, where you need to give the Profile Name. You can give any meaningful name, this might need in your feature so give some meaningful name. If you want add description you can add it in Description field and click Add button.
5. A popup will be displayed after clicking on Add button where you need to select the Account name if already exists, if not you need to click on New Account button.
6. This is very important step, you need to give the details about your email address.
Account Name : Desired Account Name (Example: MyAccount).
Description : Meaningful Description, which says about your Account (Example: This account is used to send mails to Indian Users).
Email Address : Your Email Id from which you want to send the mails.
Display Name : What name you want to display on the mail.
Reply Email : When someone wants to give reply to your mail then whom should it receive you should give their mail id.
Server Name : Mostly the server name will be like this smtp.(DomainName).com. In the domain name you need to give the name after @ in your mail id.
Lets say your mail id is abc@gmail.com, then your domain name is gmail. This is not all the cases, some times domains can be different based up on the configuration of your system.
Authentication : Its depends on you choice, mostly we use basic authentication.
Click on "OK"
NOTE: Here, I have used gmail domain so, I have checked the SSL connection.
I have wrote another post to know your existing server names check out below link.
Know your Database Mail Configuration
7. After clicking OK, you will see your profile in Database Mail Configuration Wizard where you can arrange the profiles or remove the profiles. Now, Click Next.
8. You will see "Configure SystemParameters" here, you can give the conditions in which your mail want to be. Like, you can restrict the attachments types, restrict the maximum file size and you can explore on this as of now lets go with default values. Click, Next and Finish.
9. That's all...!! You now, perfectly set up the new profile in your system. You will see the below screen upon clicking of Finish. Great .. !!
You have completed one of the major step now, all you wanted is to send the mail. Lets go and check out
Open New Query Window in SQL, Type following code and execute.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyAccount' -- This is the profile name which you created
,@recipients= 'bhkond@gmail.com' -- To whom you want to send the mail you can give ; separeator for more
,@subject= 'Mail Subject - This is a Test Mail'
,@body='Mail Body - This is an Test Mail Don''t Reply to this'
,@body_format = 'TEXT' -- This can be either TEXT or HTML
,@query = 'Select * from Table' -- If you have any query
,@attach_query_result_as_file = 1 -- Either 1 or 0; 1 for Yes Attachement 0 for No Attachement
,@query_attachment_filename= 'File_Name.xls'
You can modify the above parameters, for your reference I have mentioned some of the parameters. Always you can explore sp_send_dbmail procedure to see the what all parameters are there but, this is the simple mail with minimum required fields.
Execute the above code. Wow..!! You sent mail from SQL Server
In the next post you can find "How to Schedule a daily mail from SQL Server Or How to create a JOB"
Feel free to hit me with your comment :P :P
To send mail from SQL Server first, you need to configure the Database Mail (Mail Profile).
So, What is Database Mail or Profile ?
To send any mail we require some details like From Email and Reply Email, but internally to send mail we need server name which is nothing but domain name (for example gmail.com, live.com, Hotmail and so on..) and port number(mostly its 25 but it may change according to the server name). Why are we discussing all these details, because we fill all these details at the time of Data Mail or Profile Configuration.
Without wasting any time lets dive in to how to configure Database Mail:
1. Open SQL Server Management Studio, connect to the server on which you want to configure the Database Mail. If you don't have any servers created in your SQL Server Management Studio, try to connect local server which is default for all.
2. After connecting to the local, expand Management and right click on Database Mail you will get to see the below similar screen.
2. Now, Click on the Configuration Database Mail Click Next >. This will open Database Mail Configuration Wizard.
3. You will see "Select Configuration Task Window", based upon your requirement you need to choose the options as of now you are creating new mail profile select “Set up Database Mail by performing the following tasks: and Click Next.
4. Now, New Profile Window opens, where you need to give the Profile Name. You can give any meaningful name, this might need in your feature so give some meaningful name. If you want add description you can add it in Description field and click Add button.
5. A popup will be displayed after clicking on Add button where you need to select the Account name if already exists, if not you need to click on New Account button.
6. This is very important step, you need to give the details about your email address.
Account Name : Desired Account Name (Example: MyAccount).
Description : Meaningful Description, which says about your Account (Example: This account is used to send mails to Indian Users).
Email Address : Your Email Id from which you want to send the mails.
Display Name : What name you want to display on the mail.
Reply Email : When someone wants to give reply to your mail then whom should it receive you should give their mail id.
Server Name : Mostly the server name will be like this smtp.(DomainName).com. In the domain name you need to give the name after @ in your mail id.
Lets say your mail id is abc@gmail.com, then your domain name is gmail. This is not all the cases, some times domains can be different based up on the configuration of your system.
Authentication : Its depends on you choice, mostly we use basic authentication.
Click on "OK"
NOTE: Here, I have used gmail domain so, I have checked the SSL connection.
I have wrote another post to know your existing server names check out below link.
Know your Database Mail Configuration
7. After clicking OK, you will see your profile in Database Mail Configuration Wizard where you can arrange the profiles or remove the profiles. Now, Click Next.
8. You will see "Configure SystemParameters" here, you can give the conditions in which your mail want to be. Like, you can restrict the attachments types, restrict the maximum file size and you can explore on this as of now lets go with default values. Click, Next and Finish.
9. That's all...!! You now, perfectly set up the new profile in your system. You will see the below screen upon clicking of Finish. Great .. !!
You have completed one of the major step now, all you wanted is to send the mail. Lets go and check out
Open New Query Window in SQL, Type following code and execute.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyAccount' -- This is the profile name which you created
,@recipients= 'bhkond@gmail.com' -- To whom you want to send the mail you can give ; separeator for more
,@subject= 'Mail Subject - This is a Test Mail'
,@body='Mail Body - This is an Test Mail Don''t Reply to this'
,@body_format = 'TEXT' -- This can be either TEXT or HTML
,@query = 'Select * from Table' -- If you have any query
,@attach_query_result_as_file = 1 -- Either 1 or 0; 1 for Yes Attachement 0 for No Attachement
,@query_attachment_filename= 'File_Name.xls'
You can modify the above parameters, for your reference I have mentioned some of the parameters. Always you can explore sp_send_dbmail procedure to see the what all parameters are there but, this is the simple mail with minimum required fields.
Execute the above code. Wow..!! You sent mail from SQL Server
In the next post you can find "How to Schedule a daily mail from SQL Server Or How to create a JOB"
Feel free to hit me with your comment :P :P
