How to send E-mail/E-mail with Attachments in SQL Server TIP #97

 

In last post, TIP#96 We have configured Database E-mail. Now in this post we will see how to send mail in SQL SERVER.

Sometimes we need to send mail for different requirements like

1) Notification mails like  data inserted /updated/deleted successfully

2)Send data reports like no of amount earned, failed transaction  etc.

for this SQL SERVER provides us stored procedure “sp_send_dbMail”.

The “SP_send_dbmail” has various parameters below are some important parameters like

@profile_Name : The profile which will use to send mail

@Recipients : To whom the mails need to be send

@body : This is message body

@Subject: Subject of the mail

@Query : SQL statement which you want to share

To send a simple E-mail  we can write following statements

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @body = 'Mail sent successfully.',
    @subject = 'Mail via SQL SERVER ' ;

We can also send a query result either inline text format or html format or as a  attachment

Below query will send mail to me with count of students of a class. the below mail is simple text format mail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ;

The above mail can also be send mail as a attachment with following command

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ,
    @attach_query_result_as_file = 1 ;

We can add @body_format = 'HTML' and use various HTML tag in query or body parameters

A part from this you can cross check E-mail status whether mail is sent or not if it is not sent the what is the reason.

Below are the statements which can help to cross check mail sent status

-- Show all the emails

SELECT * FROM msdb.dbo.sysmail_allitems

-- Show all the  sent mails
SELECT * FROM msdb.dbo.sysmail_sentitems

-- show all the un sent mails
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- show all the failed mail with reason
SELECT * FROM msdb.dbo.sysmail_faileditems

I hope this might help you somewhere.

Enjoy!!

RJ

How to Configure E-mail in SQL Server Step by Step–TIP #96

 

For different reason we need to send database report , data and other SQL Server database related stuff using E-mail . SQL  Server provides E-mail functionality using “Database Email” feature. We can setup any E-mail  in few simple steps.

Let’s follow below steps

Step 1:- Open “Management” option of object Explorer in SQL SERVER  management studio.

You will find “Database Mail” option. now click on configure Database mail option as shown below

 

Step 2:- When you click on Configure database mail you will get below wizard screen

Step 3:- Click on next button then you will below screen which is basically provide you option to setup or manage existing profile . In our case we are going to create a fresh database mail so let the first default option as is as shown in below figure and click on next button

Step 4:- You will get below screen. Just give a unique profile name and click on ADD button which is highlighted

Step 5: Now this is the main setting of mail “SMTP settings”

In this screen we have to give proper E-mail address, password , SMTP server name & Port number. In below screen I used gmail settings for a my gmail account. You can use any email address

Step 6:- Once you assure that everything is perfect in above screen then click on OK button. When click Ok button this SMTP profile will be saved and you can find in below screen now click on next button

Step 7:-  If you want you can make this profile as public default profile means default this profile will be use if someone wants to send mail from sql database. Now click Next button till the end of wizard you will following screens.

 

Step7:- Now click on Finish button then your setting will be actually saved in database and below screen will appear.

Now Once you close the above screen you are good to go.

You can test this by sending a test mail to your account using following screen

I hope this steps may help you to setup database email.

Enjoy Database E-mail and try to send a test mail to me also.

Enjoy !!

RJ

Good things should be appreciated–SQL sentry Plan Explorer TIP #95

Although , It is out of box but I would like to share here. I am very choosy in food. When my mother/wife cooked food and I didn’t like the taste then I explicitly said I didn't like but when food taste was awesome then I didn't say a word.
My mother taught me “You should appreciate every thing which is good.” You’re single appreciation word do things
1. You feel yourself better
2. The person who did the good thing will be motivated to do more good things (in my case more tasty food wow )
This lesson of my mother is now one of the rule life.
So here I would like to say  one of the best tool I came across is “SQL Sentry plan Explorer”.   There are various great features of this tool but I would like to share few things which attract me more
1) It comes in two versions (Basic & professional ) .Basic version is absolutely free free free.
2) It makes SQL developer’s life easy. You can easily  cross check query plan easily.
see below snap.

You can also view logical read ,table scan and various other useful data

You can download this from http://www.sqlsentry.com/
Enjoy!!
RJ!!

Very useful SET XACT_ABORT TIP#94

SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.
Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one minute .NET or the code from which you run the query raised a command timeout.
Or we can say you have applied transaction in stored procedure and calling from .NET code and you have mention the command timeout in .NET .The ADO.NET program raised a timeout but still your query is running in background.
Now in such situation sometimes it happened the transaction which is running in SQL SERVER will be open transaction. Which might be  cause of locking problem.
In such situation  XACT_ABORT option is helpful.
By default XACT_ABORT option is off you can on it by following statement
   SET XACT_ABORT ON;
Now when this statement is on and if you face same situation as mentioned above it will rollback entire batch.
Let me explain with below example
Suppose I wrote following statement “Notice here XACT ABORT is OFF”

Now , when I run the above statements I will get following result in result window

And in the message window we will get following result

What it means instead of the error at least 3 records are inserted but sometimes you require either all or no record commit in the table then let me enable the option XACT_ABORT ON in the query

Now let me re run the statements again. When I run the above statements I got following result in message tab there is no result tab.what is means ??

It means what ever inserted is roll backed when an error occurred in a statement. Although The above example is not the best example of XACT_Abort but it will help to understand how to use it.

I appreciate your inputs on this valuable topic.
Thanks
RJ!!!

How to resolve Space issue in SQL server 2005 TIP #93

Hello friends,
Many times we face space issues with our database. To resolve this problem SQL Server 2005 provided one more solution which is VARDECIMAL feature.

** VARDECIMAL feature exist in SQL SERVER’s Developer & Enterprise edition.

** This feature is not available in SQL Server 2005’s Standard edition.
I hope by the name it is clear that what VARDECIMAL functionality is.
If not then please read below line VAR + Decimal = VARDECIMAL
It is just like VARCHAR features means it will consume space which is actual require to store the decimal value. It ignores null and zero value to save space
. To understand it more lets implement it.
I am taking an example here
First we will check the database size without applying the VARDECIMAL Feature .
To check the current space use below command
    Use Your Database name;
    GO
    Sp_spaceused;
The above command provides you current space used by the database. Now we will check whether the VARDECIMAL feature is enabled or not to check this we can use two way go to database property and check it as shown in below fig

Or
use below command
   exec sp_db_vardecimal_storage_format
Now to enable the property on particular database either make the property true in above fig
or use following command
    exec sp_db_vardecimal_storage_format ‘Your DatabaseName’, ‘ON’
Once the database has VARDECIMAL storage format.
We need to apply VARDECIMAL storage property on actual table. Before applying the feature first check the space of existing table which can be determine by following command.
   Sp_spaceused table Name;
Now check whether the table has VARDECIMAL feature enable or not. To check this feature just right click on table and check table property.
Now to enable this property use following command
   sp_tableoption ‘YourTableName’, ‘vardecimal storage format’, 1

Once you run this option on database you will find the property on the table is now true.
Now to check how much space gain you got use again
    Sp_spaceUsed table name ;
command.
Now compare the space with existing result and check how much you gain in terms of space.
I hope this feature will help you in some where if you are using SQL Server 2005 Enterprise edition.
Enjoy!!
RJ

Data Compression–A unique feature PART–II TIP #92

In last post TIP #91  we talked about  What is Data compression ? What are the  features ?  Now in this tip we will take implement the data compression with basic steps.
So lets follow the steps
1) Right click on the table on which you need to implement compression. You will get following menu just select manage compression as shown in below figure

2) Once you click on the above option you will get following screen.

3) Now you can choose compression type from dropdown of the row either Page or Row . You can calculate estimated space saving by selecting the compression type. as shown in below figure
a) Page compression (In the last column you will find requested compressed space)

b) Row Compression (in the last column you will find request compression space)

Now once you selected appropriate compression type you can click on Next button of the wizard you will get below screen

Now you can generate the script for this compression type. We can directly run the script on database itself if we are sure which with compression type.


Once we run the above command on database we are good to go and our table is compressed.
I hope this steps will help you to compress our database tables.
Enjoy compression !!
Rj !!

A Comprehensive Manual to Manage SQL Server Database

SQL Server is a uniquely designed Relational Database Management System developed by Microsoft. Its basic functionalities include storing, manipulating and retrieving data requested by multiple software applications running on a single computer or on any other machine across a network.

Details of SQL Server Database:

For storing SQL Server data individual files are created for storing data and log information.

·         Primary data file:

This file contains the information required for startup of the database and points to the other files in the database. Each database contains a single primary file that stores tables, indices and procedures. The default file extension for the primary file is .mdf.

·         Secondary data files:

These files are user defined and store the complete user data. Once the database exceeds the maximum size limit for a single Windows file, the secondary files are employed to accommodate the increasing database. The SQL server can have more than one secondary file, each with .ndf default file extension.

·         Transaction Log Files:

These files contain the log information that includes the complete modification history of the database. The Log files effectively analyze the damage causing actions and further recover the database. Each database compulsorily contains at least one log file. The default file extension for the transaction log files .ldf. Read more

Backup SQL Server Database:

Providing a backup for the complete SQL Server database to an off - site location is mandatory to protect the server data from potential catastrophe. This will help you to recover data from the following issues:

ü  Media failures.

ü  User errors, such as dropping a table.

ü  Hardware failures, such as hard disk damage or disconnection from server etc.

Let us discuss the various backup types:

·         Copy only backup:

This is a special purpose backup which is independent of regular sequence of SQL Server backups.

·         Full data backup:

This type includes backing up the entire data of a specific database, the file groups or files and the logs.

·         Differential data backup:

This backup is based on the latest full backup of a complete or partial database, files or file groups and contains the data extents changed since the previous partial backup, known as differential base.

·         Log backup:

Using this type you can create backup of the transaction logs that includes the log records.

·         File backup:

A single database file or file groups can be backed up using this type. 

·         Partial backup:

This backup contains data from a limited file groups in the database, including data in primary file group.

The backed up data can be saved on to backup devices, such as, tapes or disk files. For more details visit here

SQL Server Database Encryption:

The database is encrypted using an encryption key so as to convert the data into a non comprehensive format, referred to as cipher text. This is practiced in order to prevent unintended users from accessing the data. However, the data can be read or manipulated only by the one possessing the decryption key to convert the cipher text back into its original understandable form. SQL Server uses encryption key to protect the database, credentials, and information stored in the server. There are two types of keys- symmetric and asymmetric.

The symmetric key is generated during the initialization of SQL Server. These are the encryption keys using the same password to encrypt and decrypt data. Whereas, the asymmetric keys use different passwords for both encryption and decryption. The public key is used to encrypt data while the private key is used for decrypting. The public and private keys are created by the operating system. For more details about encryption visit here

Password Policy for SQL Server Database:

SQL Server makes use of the Windows password policy mechanisms. This policy applies to a login that is using SQL Server authentication. It also applies to a contained database user with the password. SQL Server can apply the complexity and expiration policies of Windows to its passwords. However, there are a certain guidelines to be followed while setting the password.

·         The password can be 128 characters long.

·         It should not contain the user’s account name.

·         The password must be at least eight characters long.

·         It must contain characters from three of the four categories mentioned as follows:

- Latin upper case letters (A to Z)

- Latin Lower case letters (a to z)

- Base 10 digits (0 to 9)

- Non alphanumeric characters (such as !, $, %, #).

SQL Server Transaction Log File Analysis:

Every database contains at least one transaction log file with .ldf default file extension. These transaction log files are the store for the complete modification history of the server database. It contains the transaction details of all the actions including update, insert, delete, etc. In case of any damage caused to the SQL Server database due to any modification action, the SQL transaction log file proves useful in analyzing as to which action lead to the damage, further successfully brings back the database. For more details visit blog

SQL Server Database Recovery:

Though SQL server is known to be extremely reliable, it cannot be concluded that it is immune to corruption owing to the frequent retrieving, managing or storing of data.

SQL Server facilitates a utility to backup and restore operations, provided they occur within the domain of Recovery Model for database. The database Recovery Model is a property that is responsible for controlling the transaction log maintenance. There are three recovery models available, namely, simple, full and bulk – logged. The server usually employs the full or simple recovery model to recover the sql master database. However, at any point of time, the database can be switched to another recovery model.