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

 1

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

2

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

3

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

4

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

5

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

6

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.

 

7

8

9

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

10

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

11

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.” Your single appreciation word do things

1. You yourself feel better

2. The person who did the good thing will be motivated to do more good things (in my case more tasty food wow Open-mouthed smile)

This lesson of my mother is now one of the rule life.

So here I would like to say  one of the best tools 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.

Easy_track_of_plan

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

features

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

Enjoy!!

RJ!!

LESS but it is more–CSS Preprocessor with ASP.NET

 

Now a days a “ CSS  pre processor “ is a very famous buzz word.  Although I am a web developer but still I am not always interested in writing CSS  don’t know why.

When I heard about css pre processor then I was curious to know what it exact about.

So let me share my thought here about CSS Preprocessor.

So a  CSS pre processor is a way to extend the CSS language and variables, functions and programming  stuff.

As a developer you will be more happy to have pre processor because it allows you to write code and also reduce the amount of css up to some extent.

There are many CSS  pre processor are exists in the market  like LESS, SASS,

Stylus,clay and many more.

Now Let me share here how to use LESS.css in your ASP.NET code.

Step 1:-  Add  Nuget package Less.js

Less_Nuget_Package

once you added this package in your script folder you will get 2 new java script file with prefix less as shown below in figure

Less_Script

Step 2:- Now right click on content folder and add a new item which is LESS style sheet. This file will have extension .less

 

Less

Step 3:-  Now you can write your LESS related code as shown in below snap

lesscss

If you see above snap I declared  variable like @back-color , @font-color etc

and used in body.

Step 4: Now  add a web page and write following lines to enable LESS css.

<link href="Content/MyLessCss.less" rel="stylesheet/less" />
   <script src="Scripts/less-1.5.1.min.js"></script>

Test_Less_Page

step 5:- Now in web.config we need to add following mime type

Webconfig_less

Or you can add this from IIS.

Step 6: Now we are good to go and can run  it

sample_Less__page

So we get the color according to variable which we declared.

It means we can play with the variables and can write the less code in programming way.

I hope this post will give a step to go further and I am sure you will deep dive in CSS pre processor soon.

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 a 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

image

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

image

And in the message window we will get following result

image

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

image

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 ??

image

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

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

menu

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

wizard1

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)

Page_Compression

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

Row_compression

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

datacompressoionwizard

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.

row_data_compression_command

table_compression_command

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

Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space.

It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” is one of the best option.

This feature introduce in SQL SERVER 2008 onwards with Enterprise & Developer Edition.

By the name it is clear that it compress the data and provide more space on disk.

There are 2 options available in Data compression

Row level compression:

 Row level compression primarily turns fixed-length data-types into variable data-types, thereby saving space. It also ignores zero and null values saving additional space. Because of this, more number of rows can be accommodated in a single data page.

Page level compression:-

Page level compression initially performs Row Level compression and adds two additional compression features – Prefix and Dictionary Compression. Page level compression offers better space saving than row level compression. Although the page level compression produce more space but obvious the CPU utilization is higher in page level compression. Overall we can say the page level compression is highly CPU consuming operation means it involves lot of  CPU efforts for compress & de compression. So choosing this option requires a high CPU & RAM configuration machine. Due to this feature it mostly used in data warehouse database where repeated values occurs. using this option with a table where lots of write operation occurs in day to day activity will be a bad idea.

Now we aware of data compression and its two options. Now lets discussed some of the major points before using this valuable feature

1) Data compression is SQL SERVER feature so we don’t need to do any tweak or rewrite our application code which is a good thing.

2) Compression is real time so again you don’t need to re run a maintenance job each time to compress the data.

3) As mention earlier you have to pay bit CPU cost for compression.

4) with Compression data files MDF files, ldf files and buffer cache size can be improve.

5) Compression not compress FILESTREAM data object which is obvious.

6) Although maintenance of compressed index is higher.

In next post I will show simple steps for implementing this best feature.

Enjoy !!!

Thanks

RJ!!!

Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT ‘Infinite Loop’
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

sp_who2_Inadiandotnet

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Kill_Process_Indiandtnet

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!

Secure your password with HashBytes TIP #89

Dear Friends,

In my last article (TIP 87) I wrote about PWDENCRYPT.  I forgot to write few details but thanks to all my talented friends & blog readers who guided me by providing there feedback on post.

I respect their inputs and always interested to get more inputs. Thanks to all of you.

Now I would like to share information about “HASHBYTES”  function which is also available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below

HASHBYTES (ALGORITHM, ‘INPUT WHICH YOU WANT TO SECURE’)

Now let see an example to understand it more

HASHBYTES

Just wanted to add here that SHA2_256, SHA2_512 available with 2012 version and above.

Someone said this line right “More option More confusion”

Now we have different algorithm then which one we have to use so the answer is (according to my knowledge) use the latest most secure one Like SHA1, SHA2 etc.

I hope this article may be useful to you.

Thanks !!!

RJ!!!

PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES (‘Indiandotnet’,PWDENCRYPT(N’MyPassword’))
INSERT INTO @tblLogin VALUES (‘SQLRaaga’, PWDENCRYPT(N’Test’))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N’Test’,EncryptedPassword) = 1

For detail  take a look of below snap

PWDCompare_Indiandotnet

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!

How to Encrypt password in SQL Server ? TIP #87

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

Now this article will help you to encrypt your password in hash. Isn’t it interesting ?

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function is PWDENCRYPT.  By the name it is clear that it will crease the password.

The syntax is very simple PWDENCRYPT(N’String which you want to encrypt’)

see below snap for more detail.

PWDEncrypted_Indiandotnet

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ