How to send email using SQL Server

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 

Dot Net Interview Questsions

1. Difference between static read-only and const?
2. Dependency injection?
3. How do you implement Logging?
4. What is a session and what are the session types?
5. What are the different types of authentication?
6. How do you implement authorization?
7. What is a static constructor?
8. How do you use httpclient ?
9. How what is routing?
10. How do you implement environment based configuration?

Find the all stored procedures where a table is being used by query in ORACLE

How to know whether table is replicated or not in SQL Server

SELECT publisher,Publisher_Db,publication,article
FROM dbo.MSreplication_objects
where article = 'Table_Name' 

Linq query between two lists | Update one list with another list based on a condition

Let's say we have 2 lists ListOne and ListTwo and we want to update the ListOne.Name with ListTwo.Name whenever there is a match with the ID in the both lists using linq then below is the simplest query

Search a table name in all databases at once

It's very difficult to select each and every database and search for the table from sys.tables something look like below 

Select * from sys.tables where name like '%TableName_Here%'

What if you don't know the exact database, do you search all the databases to find the table?

if your database server has 100 database's then it will be a very tedious task for you to select a database and search so to avoid such pain below is the procedure you can execute this procedure in any of the databases and execute the procedure by giving the table name as a parameter.

Hope the above procedure will solve your problem cheers... !!!

Super easy mocking with NSubstitute | Dot Net Recipe

In this post, I will share how to super easy mocking with NSubstitute. First, let's understand the use of mocking and why we need to mock the methods.  In any project, if you are required to write unit test cases then the project is not having any external calls like DataBase or Services then it is easy to write unit test cases, 

Let's understand what is external call, any method call which is not under our project can be called as an external call.

But in real time projects, it's not the case where the project involves at least some external calls it may be DB or other service or something else. 

Any unit test case which you write is required to execute in within the milliseconds, what if it takes more that one minute, then that is the bad practice of writing the unit test cases.

So what's all the point of talking about external calls and unit test case execution performance? I will tell you,

Let's say your unit test case contains any DB call which will get the data for you then that call will take the maximum amount of time in your unit test, so drastically it will reduce the test case execution performance. so you want to eliminate that DB call? 

But from where you get the data here comes the concept of Mocking where you will mock the DB call and return the likewise data which DB will return.

So to mock a method you will need a mechanism, here I am going to explain that mechanism with NSubstitute.


I have created a sample demo project where it will read the student marks from the DB and calculates the percentage of the student. 

So now, I want to write a  test case for the CalculateStudentPercentage Method. You can create Unit test project from "Add New Project"  Window or you can directly "right click" on the method and select "Create Unit Tests",

To use the NSubstitute, firstly you need to install the NSubstitute package

Install-Package NSubstitute

I have the following DataManager.cs Class and Interface IDataManager.cs where it will be used to call the DB and get the results

and I have Processor.cs class where it will have the business logic in your application and you need to write the test cases for the methods which are there in this class. As of now we have one method CalculateStudentPercentage

The only requirement is, we need to have an Interface architecture for your processor so that we can pass NSubstitute to the processor.

Let's write a unit test case for the above method with mocking the DataManager method call using NSubstitute. In the below, we have created a substitute for the IDatamanager and we are returning mockMarksList instead of the original DB Results.

Whenever we have a DB call we are returning the mock data which we have created in the test method. In the below method we have 2 steps

• Creating an NSubstitute.
• Invocking the Substitute method and returning the mock data.

Now we have just mocked the GetStudentMarks method and we have returned our mock data instead of the actual data like these you can avoid the external calls. If you any doubts and comments feel free to comment.