SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for May, 2013

SQL Server – Configure Database Mail to Send Email Using Gmail or Live Account

Posted by Prashant on May 24, 2013


In this post we will see how to configuring SQL Server Database Mail for sending mails using Gmail Account or Live Mail account. By using Database Mail a database application can send e-mail messages to users with SQL query results and also include additional attachment files in either plain text or html format.

In order to configure the database mail here are the steps:

1. Connect to Object Explorer → Expand Management folder → Right click  on Database Mail → Select Configure Database Mail

dbmail1

2. Click Next → In select configuration task window select Set up Database Mail by performing following tasks:

dbmail3

3. Click Next → In new profile window enter Profile Name and Description to identify the profile → Click Add…

dbmail4

4. In Add Account to Profile…. window select New Accoount

dbmail5

5. In New Database Mail Account window provide details of the email account which you want to use to send emails.

dbmail6

If you are using gmail account:

Account Name: Gmail
Description: Gmail account used for db mail
E-mail Address: emailid@gmail.com
Display Name: DBMail (you can specify whatever name that suits you)
Reply e-mail: emailid@gmail.com (any e-mail account you like to use)
Server Name: smtp.gmail.com
Port Number: 587
Check the This Server requires a secure connection (SSL)
Select Basic Authentication and provide your user name and password for the outgoing email account that you used the above step
Click OK → Next

If you are using Live mail account

Account Name: LiveMail
Description: Live mail account used for db mail
E-mail Address: emailid@live.com
Display Name: DBMail (you can specify whatever name that suits you)
Reply e-mail: emailid@live.com (any e-mail account you like to use)
Server Name: smtp.live.com
Port Number: 25
Check the This Server requires a secure connection (SSL)
Select Basic Authentication and provide your user name and password for the outgoing email account that you used the above step
Click OK → Next

More detail about smtp server setting refer Gmail SMPT Settings and Live SMTP Settings

6. In Manage Profile Security you can select the profile that you wants to make as public profile which can be accessible by all users of any mail host database.

dbmail8

7. Configure System Parameters here you can modify the default parameter settings as per your need e.g restricting file types not to allow in mail attachment, max size of file…etc.

dbmail9

8. Click Next → Finish

Upon on finish it will configure the database mail settings.

dbmail10

9. Click Close and you are done with the database mail configuration.

10. Once this is done try to send a test mail to check the email.

dbmail11 dbmail12

Here the email will be queued then will be processed by database mail. If you find this post helpful feel free to comment and share.

Posted in SQL Server | Tagged: , , , , | 1 Comment »

SQL Server – Date Formats

Posted by Prashant on May 22, 2013


Database developers commonly ask for date formats while comparing date columns, showing dates in different format and etc using SQL query. In this post we will see a consolidated list for various date formats that are available and we use in our queries.

Format

Standard

Query

Output

Mon dd yyyy hh:mmAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 0) May 22 2013  8:54PM
Mon dd yyyy hh:mmAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) May 22 2013  8:55PM
mm/dd/yy U.S SELECT CONVERT(VARCHAR(8), GETDATE(), 1) 05/22/13
mm/dd/yyyy U.S SELECT CONVERT(VARCHAR(10), GETDATE(), 101) 05/22/2013
yy.mm.dd ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) 13.05.22
yyyy.mm.dd ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) 2013.05.22
dd/mm/yy British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) 22/05/13
dd/mm/yyyy British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) 22/05/2013
dd.mm.yy German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) 22.05.13
dd.mm.yyyy German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) 22.05.2013
dd-mm-yy Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) 22-05-13
dd-mm-yyyy Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 22-05-2013
dd mon yy SELECT CONVERT(VARCHAR(9), GETDATE(), 6) 22 May 13
dd mon yyyy SELECT CONVERT(VARCHAR(11), GETDATE(), 106) 22 May 2013
Mon dd, yy SELECT CONVERT(VARCHAR(10), GETDATE(), 7) May 22, 13
Mon dd, yyyy SELECT CONVERT(VARCHAR(12), GETDATE(), 107) May 22, 2013
hh:mm:ss SELECT CONVERT(VARCHAR(8), GETDATE(), 8) 21:04:29
hh:mm:ss SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 21:04:58
Mon dd yyyy hh:mm:ss:mmmAM (or PM) Default + seconds + miliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 9) May 22 2013  9:08:20:677PM
Mon dd yyyy hh:mm:ss:mmmAM (or PM) Default + seconds + miliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 109) May 22 2013  9:08:32:853PM
mm-dd-yy USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) 05-22-13
mm-dd-yyyy USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) 05-22-2013
yy/mm/dd Japan SELECT CONVERT(VARCHAR(8), GETDATE(), 11) 13/05/22
yyyy/mm/dd Japan SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 2013/05/22
yymmdd ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) 130522
yyyymmdd ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) 20130522
dd mon yyyy hh:mi:ss:mmm Europe SELECT CONVERT(VARCHAR(30), GETDATE(), 13) 22 May 2013 21:11:25:890
dd mon yyyy hh:mi:ss:mmm Europe SELECT CONVERT(VARCHAR(30), GETDATE(), 113) 22 May 2013 21:11:36:110
hh:mm:ss:mmm SELECT CONVERT(VARCHAR(16), GETDATE(), 14) 21:11:53:673
hh:mm:ss:mmm SELECT CONVERT(VARCHAR(16), GETDATE(), 114) 21:12:40:130
yyyy-mm-dd hh:mi:ss SELECT CONVERT(VARCHAR(19), GETDATE(), 20) 2013-05-22 21:13:15
yyyy-mm-dd hh:mi:ss SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 2013-05-22 21:13:25
yyyy-mm-dd hh:mi:ss.mmm SELECT CONVERT(VARCHAR(23), GETDATE(), 21) 2013-05-22 21:13:34.970
yyyy-mm-dd hh:mi:ss.mmm SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 2013-05-22 21:13:45.093
mm/dd/yy hh:mm:ss AM (or PM) SELECT CONVERT(VARCHAR(20), GETDATE(), 22) 05/22/13  9:14:09 PM
yyyy-mm-dd SELECT CONVERT(VARCHAR(26), GETDATE(), 23) 2013-05-22
hh:mm:ss SELECT CONVERT(VARCHAR(8), GETDATE(), 24) 21:14:31
yyyy-mm-dd hh:mm:ss:mmm SELECT CONVERT(VARCHAR(26), GETDATE(), 25) 2013-05-22 21:15:21.130
yyyy-mm-dd hh:mm:ss:mmm ISO8601 SELECT CONVERT(VARCHAR(27), GETDATE(), 126) 2013-05-22T21:15:36.543
yyyy-mm-ddThh:mi:ss.mmm ISO8601 with time zone SELECT CONVERT(VARCHAR(30), GETDATE(), 126) 2013-05-22T21:34:57.127
dd mon yyyy hh:mi:ss:mmmAM(Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.) Hijri SELECT CONVERT(NVARCHAR(50), GETDATE(), 130) 13 رجب 1434  9:18:26:580PM
dd/mm/yy hh:mi:ss:mmmAM(Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.) Hijri SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 13/07/1434  9:15:58:400PM

Refer msdn for more details on date formats.

Posted in SQL Server | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: