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 

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' 

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... !!!

How to enable SQL Server IntelliSense | Troubleshooting SQL Server IntelliSense

Intellisense is the most useful feature in SQL Server. Full stack developers who are working on the SQL Server Management Studio they will know the pain if intellisense is not working, because they are habituated with the other IDE's which are much powerful in intellisense than SQL Server Management Studio. Let's find out how to enable the Intellisense feature. 

1. Open SQL Server Management Studio
2. Go to Tools Or Click Alt + T and click Options
3.  Under Text Editor click on Transact - SQL and IntelliSense
4. Check "Enable IntelliSense" and click "OK".


Want to check IntelliSense is enabled or not ?

1. Open SQL Server Management Studio
2. Go to Query Or click Alt + Q,
3.  You will find an option "IntelliSense Enabled", see whether that is highlighted or not.


IntelliSense is stopped working in between, don't worry we have an option to make it work again.

1. Open SQL Server Management Studio
2. Got to Edit or click Alt + E
3. Now, click on IntelliSense and click "Refresh Local Cache"
4. Or you can use Shortcut key "Ctrl + Shift + R"

Still, Intellisense is not working uff, you got thug life then. There could be various reasons for this check out your reason. 

1. Have you installed any recent Microsoft products like visual studio 
2. Have you upgraded your SQL Server Management Studio to higher version. 
3. Have you uninstalled and again installed your SQL Server Studio

Could be other reasons also above are the most common,

Now, what to do ??

We live in a technology where we get everything in a form of plugins, you can install third party plugins which enables IntelliSense without doing any settings, one-of the most liked plugin is SQL Complete you can download the basic version for free. If you are rich enough then you can buy the premium :P :P

click on the below link, download and install. Enjoy free IntelliSense.

SQL Complete

How to update multiple columns with multiple rows with different values using single query

Let's say a table named Audit_Table have 3 columns: AuditId, Old value and New Value 

Select * from Audit_Table

AuditId     Old Value                             New Value
----------- ----------------------       ------------------------
1             1                                        2   
2             3                                        4 
2             5                                        6
3             7                                        8


And, we have one more table named Department_Table with the following columns DepartmentId and Department Name

Select * from Department_Table

DepartmentId  Department_Name
------------        ---------------
1                       Computers 
2                       Electrical
3                       Civil     
4                       Mechanical
5                       Electronic
6                       Chemical  
7                       Bio - Tech
8                       Embeded   

In the above Audit_Table, Old Value and New Value having the values of Department id's and you want to update those values with the Department Names. But you want to update the Audit table with a single query, you don't want to run multiple update statements

Below is the query for which we need top update multiple columns and multiple rows with different values

UPDATE Audit_Table SET [Old Value]= (SELECT Department_Name
                                 FROM Department_Table 
                                 WHERE [Old Value] = DepartmentId),
                          [New Value]= (SELECT Department_Name
                                 FROM Department_Table 
                                 WHERE [New Value] = DepartmentId)

And, output goes like this, in a single query you have updated multiple columns and 'n' number of rows with a different values. 

Select * from Audit_Table

AuditId     Old Value                                          New Value
----------- ----------------------------------- ------------------------
1             Computers                                          Electrical
2             Civil                                                     Mechanical
2             Electronic                                            Chemical  
3             Bio - Tech                                           Embeded   

Must Declare a Scalar Variable Error - Solved | How to insert apostrophe in SQL Query String

You have to use double apostrophe to insert a single apostrophe in SQL Query String. Below is the sample query where it tries to send mail, in which we have to write a SQL query to fetch the details.

In the SQL query we are using a scalar variable, If we are not converting that in to string format SQL throws an error saying "Must declare a scalar variable" and you cant escape from that error

Lets say, we are using a date time value in where condition then its need to be under apostrophes, when we get the value from scalar variable then that value should be converted in to char to form complete SQL query string.

Instead of doing all patch-ups, you can easily convert that value to character and insert in to the query as below.. 


DECLARE @scalarVariable DATETIME

SET @scalarVariable  = (SELECT SomeValue FROM SomeTable )


DECLARE @tab CHAR(1)= CHAR(9);
DECLARE @fileName VARCHAR(256);
DECLARE @sql nvarchar(MAX);
DECLARE @subject nvarchar(MAX);

SET @fileName = 'SomeFile.xls';
SET @sql = N'SELECT * FROM SomeTable WHERE SomeColumn  >' + ''''+CONVERT(VARCHAR(24),@scalarVariable ,21)+'''';


SET @subject = 'Some Subject';

EXEC msdb.dbo.sp_send_dbmail
@profile_name='SomeProfile'
,@recipients='abcd@gmail.com'
,@subject= @subject
,@body='Some data have been changed'
,@body_format = 'Text'
,@query = @sql
,@query_result_separator =  @tab
,@attach_query_result_as_file = 1
,@query_attachment_filename= @fileName
,@query_result_width = 32767
,@query_result_no_padding = 1

Know your current DataBase Mail Configuration

Its very important to give the correct @Profile_Name which are configured for a Data base while we are executing the sp_send_dbmail.

Sometimes, everything seems to be correct, even though you wont receive mail and you could not figure out what could be the issue.

So, first get to know are you giving correct @Profile_Name or not,  that you can see using the following predefined system functions

EXEC msdb.dbo.sysmail_help_principalprofile_sp;


When we executes the above query you will see different @Profile_Names which are configured.

What is SET XACT_ABORT ON | OFF

If you have a 10 SQL statements in a stored procedure, where 10th statement is an update statement which updates time in a table indicating that on that particular time that stored procedure got executed successfully. 

Lets consider a situation where 5th statement got run-time error which leads incorrect results in stored procedure. 

What happens now, ?

Will the rest of the statements executes till the 10th statements and causing the update to a table saying the job got executed successfully ? The answer is YES

In real scenario this should not happen when ever there is an run-time error the JOB needs to stop at that moment 

Why this background ? 

To stop the execution of the job at that moment of run-time error we use 

SET XACT_ABORT ON 

Before the starting of the Stored Procedure

SET XACT_ABORT OFF

After the last statement of the Stored Procedure.

SQL: How to DELETE or UPDATE large data in chunks to increase the performance with no locking on table

Let's say you want to update or delete large data think of millions and millions of records, you write a single update or delete statement. It will downgrade your query performance or unnecessarily it will hold the lock on the table which will cause the applications which are using that table will be in stuck. 

So to avoid such type of situations, we can delete or update the data in chunks which will help in better performance than the previous case. 

Let's say you have a table like below, here the data is less I have taken this for demo purpose. 

And we want to delete in the chunks of 2 which means at a time only 2 records should get updated or deleted.

Below is the sample query using while loop, first you declare a variable @rowcount = 1, and in the update statement, you are specifying the count in which amount of chunks it needs to be updated this can vary based on your requirement. and in the end, you will update @rowcount with the @@ROWCOUNT. 

You can see the result, how the records got updated. 

SQL Can BIT data type in SQL Server allows to insert NULL??

Don't get surprised with the answer but the answer is "Yes" :D, BIT data type allows NULL value to insert. As we know that BIT can store only 1's and 0's or True and False respectively, but when we don't specify the NULL check while we create a table, then system will automatically allows you to store the NULL Values. To avoid this, create  a column with condition that it should not allow the NULL values which means explicitly you are saying to system that, hey SQL don't store any null values in my column.