Sending Email using SQL Server

Follow the below steps to configure email account and send email using SQL Server

–Enabling Database Mail
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘Database Mail XPs’,1
reconfigure

–Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLProfile’,
@description = ‘Mail Service for SQL Server’ ;

— Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQL_Email_Account’,
@email_address = ‘abc@gmail.com’,
@mailserver_name = ‘smtp.gmail.com’,
@port=587,
@enable_ssl=1,
@username=’abc’,
@password=’password’

— Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLProfile’,
@account_name = ‘SQL_Email_Account’,
@sequence_number =1

— Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘SQLProfile’,
@principal_id = 0,
@is_default = 1 ;

–Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’,
@recipients = ‘abc@gmail.com’,
@body = ‘Database Mail Testing…’,
@subject = ‘Databas Mail from SQL Server’;

–Verifying, check status column
select * from sysmail_allitems

 

#send-email

Exception Handling

The error-handling model in ASP.NET lets you handle errors easily at the method, page, and application levels of your web applications. Most applications will use some combination of these to handle problems when they arise.

Method level: In method you can handle the exception using try…Catch…Finally block.

private void anyRoutine( )
{
	try
	{
		// Routine code in this block
	}
        catch (Exception exc)
	{
	        // error handling in this block
	}
	finally
	{
		// cleanup performed in this block
	}
} // anyRoutine

Page level: Why would you want to use this approach? It allows you to handle all page-level errors in a uniform way, which can simplify error-handling code and make it more consistent and robust. The trick is in keeping all the error-handling code in one place in the original page by leveraging the Page_Error method

private void Page_Error(Object sender,
			System.EventArgs e)
{
	Exception lastError = null;
	// get the last error that occurred
	lastError = Server.GetLastError( );

	// do any logging, notifications, etc. here

	// set the URL of the page that will display the error and
	// include querystring parameters to allow the page to display
	// what happened
	Page.ErrorPage = "ErrorPage.aspx" +
		 "?PageHeader=Error Occurred" +
		 "&Message1=" + lastError.Message +
		 "&Message2=" +
		 "This error was processed at the page level";
 } // Page_Error

Application level: By handling all exceptions at the application level, rather than at the method or page level, you can process all errors for the application in a single location. Doing all error handling in one place in an application is key to writing effective code.

protected void Application_Error(Object sender, EventArgs e)
{
	const String EVENT_LOG_NAME = "Application";

	Exception lastException = null;
	System.Diagnostics.EventLog log = null;
	String message = null;

	// get the last error that occurred
	lastException = Server.GetLastError( );

	// create the error message from the message in the last exception along
	// with a complete dump of all of the inner exceptions (all exception
	// data in the linked list of exceptions)
	message = lastException.Message +
			  "\r\r" +
			  lastException.ToString( );

	// Insert error information into the event log Or Log the Exception in text file
	// perform other notifications, etc. here
	

	// clear the error and redirect to the page used to display the
	// error information
	Server.ClearError( );
	Server.Transfer("ErrorPage.aspx" +
			"?PageHeader=Error Occurred" +
			"&Message1=" + lastException.Message +
			"&Message2=" +
			"This error was processed at the application level");
} // Application_Error

#exception-handling

Selecting the Right Tabular Control

Problem
You want to use an ASP.NET control to display some data in a tabular format.

Solution
Use a Repeater, DataList, DataGrid, or GridView control. Always choose the smallest and fastest control that meets your needs, which invariably will be influenced by other criteria as in these examples:

If you need a quick and easy solution
Use a GridView.

If you need a lightweight read-only tabular display
Use a Repeater.

If you need your solution to be small and fast
Use a Repeater (lightest) or DataList (lighter).

If you want to use a template to customize the appearance of the display
Choose a Repeater or DataList.

If you want to select rows or edit the contents of a data table
Choose a DataList, a DataGrid, or a GridView.

If you want built-in support to sort your data by column or paginate its display
Choose a GridView.

If you want to use custom pagination
Choose a DataGrid.

Comparative summary of native tabular control features

Feature

Repeater control

DataList control

DataGrid control

GridView control

Default appearance

None (template-driven)

Table

Table

Table

Automatically generates columns from the data source

No

No

Yes

Yes

Header can be customized

Yes

Yes

Yes

Yes

Data row can be customized

Yes

Yes

Yes

Yes

Supports alternating row customization

Yes

Yes

Yes

Yes

Supports customizable row separator

Yes

Yes

No

Yes

Footer can be customized

Yes

Yes

Yes

Yes

Supports pagination

No

No

Yes

Yes

Supports custom paging

No

No

Yes

No

Supports sorting

No

No

Yes

Yes

Supports editing contents

No

Yes

Yes

Yes

Supports selecting a single row

No

Yes

Yes

Yes

Supports selecting multiple rows

No

No

No

No

Supports arranging data items horizontally or vertically (from left-to-right or top-to-bottom)

No

Yes

No

No

Supports sorting and paging using asynchronous callbacks

No

No

No

Yes

#tabular-controls

Regular Expression – II

//Matches string start with pattern
String strData = “Hello there”;
Regex rg = new Regex(“^Hello”);
bool truefalse = rg.IsMatch(strData);

rg = new Regex(“^Hola”);
truefalse = rg.IsMatch(strData);

//Matches strung end with pattern
strData = “Hello there”;
rg = new Regex(“there$”);
truefalse = rg.IsMatch(strData);

//Matches char from the list
rg = new Regex(“[aeiou]”);
truefalse = rg.IsMatch(strData);

//Not matches char from the list
rg = new Regex(“[^aeiou]”);
truefalse = rg.IsMatch(strData);

//Matching range
strData = “Hello there, you are 8 years old.”;
rg = new Regex(“[0-9]”);
truefalse = rg.IsMatch(strData);

strData = “55”;
rg = new Regex(“[0-9][0-5]”);
truefalse = rg.IsMatch(strData);

//Match string contains A-Z, a-z and 0-9
strData = “Hello”;
rg = new Regex(“[^a-zA-Z0-9]”);
truefalse = rg.IsMatch(strData);

//Match string contains A-Z, a-z, 0-9 with white space, below code return false when string contains chars other than defined.
strData = “hello there 0″;
rg = new Regex(@”[^a-zA-Z0-9\s]”);
truefalse = rg.IsMatch(strData);

//Matches a-z and A-Z
strData = “hello there”;
rg = new Regex(@”[^\w\s]”);
truefalse = rg.IsMatch(strData);

//Matches a-z, A-Z and 0-9
strData = “hello there 0″;
rg = new Regex(@”[^\w\d\s]”);
truefalse = rg.IsMatch(strData);

//Note*: \w represet a-zA-Z, \W other than word characters and \d represent 0-9, \D other than non-decimal
//\s represent whitespace

//To match the number of characters that matches certain pattern.

//True scenario
strData = “12345”;
rg = new Regex(@”\d{5}”);
truefalse = rg.IsMatch(strData);

strData = “12345-6789″;
rg = new Regex(@”\d{5}-\d{4}”);
truefalse = rg.IsMatch(strData);

//False scenario
strData = “12345-678″;
rg = new Regex(@”\d{5}-\d{4}”);
truefalse = rg.IsMatch(strData);

strData = “12345-678A”;
rg = new Regex(@”\d{5}-\d{4}”);
truefalse = rg.IsMatch(strData);

//Minimum and Maximum Occurences
//True scenario
strData = “123”;
rg = new Regex(@”\d{1,3}”);
truefalse = rg.IsMatch(strData);

strData = “12”;
rg = new Regex(@”\d{1,3}”);
truefalse = rg.IsMatch(strData);

//False scenario – string end with numeric
strData = “A124B”;
rg = new Regex(@”\d{1,3}$”);
truefalse = rg.IsMatch(strData);

#regular-expression

SQL Server Basic – VII

–Joining two tables without a clause i.e, cross products for two tables

USE Pubs
SELECT * from authors,titleauthor
GO
SELECT * from authors cross join titleauthor
GO

This query is not actually joined on any common clause. It is a cartesian product, so if there are 20 records in first table
and 30 records in the second we will get a total of 600 records.
Not putting a where clause is same as a CROSS JOIN.

–Using cross products to generate unique derived fields

USE Pubs
SELECT distinct (stor_id + '-' + au_lname) compfld
from stores cross join authors
order by compfld

‘+’ operators concatenates two fields for composite field generation Primary key of two different tables can be used to create
a composite key in a link table this way.

–Using Inner Joins to find exact matching rows

SELECT au_id,pubdate
from titleauthor inner join titles
ON titleauthor.title_id = titles.title_id

INNER JOIN exactly matches the common key on either sides of the Join

–Conventional Inner Joins

SELECT au_id,pubdate
from titleauthor, titles
WHERE titleauthor.title_id = titles.title_id

–Inner Joins having more than two tables

SELECT   au_id,UPPER(pub_name) PUBLISHER,pubdate [Published On]
from       titleauthor
INNER    JOIN titles
ON          titleauthor.title_id = titles.title_id
JOIN      publishers
ON          publishers.pub_id = titles.pub_id
ORDER   BY PUBLISHER

INNER JOIN is the default type of Join. Specifying it is not always needed

–Inner Joins having more than two tables refined with WHERE, AND & OR

SELECT au_id AUTHOR,UPPER(pub_name) PUBLISHER,pubdate [Sys Pub Date],
Convert(char(12),pubdate) [PUBLISHED ON]
from titleauthor
JOIN titles
ON titleauthor.title_id = titles.title_id
JOIN publishers
ON publishers.pub_id = titles.pub_id
WHERE pub_name like 'ALGO%'
AND au_id >= '3%'

Multiple Inner Joins further refined by a Where / And Clause Convert function gives the date in a more friendly format.
Try : Convert(char(20),pubdate) or Convert(char(6),pubdate)
‘>=3%’ will give all the results with au_id starting from 3??-??-???? and above in a lexical order

–Left Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titles a
left join titleauthor b
ON a.title_id = b.title_id
order by a.title,b.au_ord

Left Join brings all the rows from table appearing first in the JOIN clause

–Right Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titleauthor b 
right join titles a
ON a.title_id = b.title_id
order by a.title,b.au_ord

Right Join brings all the rows from table appearing second in the JOIN clause.

–LeftJoin in two tables having a filter

select                    stor_name,CAST(discount AS VARCHAR) + '%' '%'
from                       discounts
LEFT JOIN              stores
ON                          discounts.stor_id = stores.stor_id
WHERE                   discounts.stor_id IS NOT NULL

The results can be filtered using a trailing Where.
Left join brings all the records from discounts & nulls for unrelated stores records CAST does the same jobs as convert but lacks formatting options Casting needed as % is a character.
Here first ‘%’ becomes part of the data & the second becomes header of the column.

#sql-basic

SQL Server Basic – VI

–Finding the Grouped SUM based on several filtering criteria

SELECT   STOR_ID [Store Id],SUM(QTY) [Total Quantity]
FROM     sales
WHERE   title_id like 'PC%'
AND       Stor_id between 7000 and 8000
OR          ord_date between '1994-01-01' and '1995-01-01'
GROUP BY STOR_ID

SUM acts just like Count, but instead of number of rows, accumulates the content of any numeric field like QTY here.

–Finding the Average based on several filtering criteria

select avg(discount) avg_discount from discounts
go
SELECT   PAYTERMS,AVG(QTY) [Average Quantity]
FROM     sales
where ord_date < '1994-01-01'
GROUP BY PAYTERMS

AVG is also a grouping function and would average out the quantity against the grouping parameter..

–Grouping By an expression containing a column

select distinct(Left(upper(name),1)) TABLE_INITIALS, count(*) OCCURENCES
from SysObjects
where xtype = 'U'
group by Left(upper(name),1)

Assigning priorities in filtering a query with the help of proper bracketting (parenthesis)

SELECT * from Pubs..sales
WHERE title_id like 'PC%'
AND (
Stor_id between 7000 and 8000
OR ord_date between '1994-01-01' and '1995-01-01'
)
-----
SELECT * from Pubs..sales
WHERE (
title_id like 'PC%'
and Stor_id between 7000 and 8000
)
OR ord_date between '1994-01-01' and '1995-01-01'

In the first query the bracketted WHERE clause gives 2 results and the AND clause gives 19 results. (select * from Pubs..sales where Stor_id between 7000 and 8000 OR ord_date between ‘1994-01-01’ and ‘1995-01-01’ gives 19 results). There intersection gives only 1 result as common.

–In the second query the bracketted WHERE clause gives 1 result
–and the OR clause gives 8 results.Since this is a cumulative OR clause,
–we would get 9 rows in total.

#sql-basic

SQL Server Basic – V

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’
OR  RIGHT(Postalcode,2) = ’23’

–Selecting columns based on a Where clause which is further constrained by a BETWEEN clause
SELECT Au_LName, YTD_SALES
from Pubs..titleView
where ytd_sales between 2000 and 4000
order by 2

–Finding the Grouped Count based on several filtering criteria
Use Pubs
SELECT STOR_ID, COUNT(*) count
FROM sales
WHERE title_id like ‘PC%’
AND Stor_id between 7000 and 8000
OR ord_date between ‘1994-01-01’ and ‘1995-01-01′
GROUP BY STOR_ID

–GetDate( ) processing with integral days processing
select getdate(), getdate()-1, getdate() + 365

–GetDate( ) processing  with fractional days increment
select getdate() + 1.5
go
select getdate() + 1.45
go

–DateAdd( ) function processing
select hire_date,dateadd(yy,2,hire_date)
from pubs..employee

select hire_date,dateadd(yyyy,2,hire_date)
from pubs..employee

–DateAdd( ) function processing for year increments
select ’15-Aug-1930′ birth_day,dateadd(yy,60,’15-Aug-1930′) retirement_day

–DateAdd( ) function processing for various input date formats
select dateadd(yy,60,’1930-12-13′),
dateadd(yy,60,’1930.12-13′) ,
dateadd(yy,60,’Dec,13 1930′)

–DateAdd( ) function processing for days & weeks increments
select dateadd(dd,-365,hire_date)
from pubs..employee
go

select dateadd(ww,1000,hire_date)
from pubs..employee
go

–DateAdd( ) function processing for months, minutes and seconds increments
Select DATEADD(mm,-3,getdate( ))

Select DATEADD(ss,86400,getdate( )) as Tommorow_Now

Select RIGHT(DATEADD(mi,150,getdate( )),8) as TestEndTime

–Date Conversion formats- Specifying SQL Output formats
select convert(varchar,getdate(), 1)          –Output in mm/dd/yy

select convert(varchar,getdate(), 2)          — Output in yy.mm.dd

select convert(varchar,getdate(), 3)          — Output in dd/mm/yy

select convert(varchar,getdate(), 4)          — Output in dd.mm.yy

select convert(varchar,getdate(), 5)          — Output in dd-mm-yy

select convert(varchar,getdate(), 6)          — Output in dd Mon yy

select convert(varchar,getdate(), 7)          — Output in Mon dd, yy

select convert(varchar,getdate(), 8)          — Output in hh+:mi:ss

select convert(varchar,getdate(), 9)          — Output in Mon dd yyyy hh:mi:ss:mmmXM

select convert(varchar,getdate(), 10)       — Output in mm-dd-yy

select convert(varchar,getdate(), 11)       — Output in yy/mm/dd

select convert(varchar,getdate(), 12)       — Output in yymmdd

select convert(varchar,getdate(), 13)       — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(), 14)       — Output in hh+:mi:ss:mmm

–Date Conversion formats in extended Year notation – specifying SQL Output formats
select convert(varchar,getdate(), 101)     — Output in mm/dd/ yyyy

select convert(varchar,getdate(),102)      — Output in yyyy.mm.dd

select convert(varchar,getdate(),103)      — Output in dd/mm/yyyy

select convert(varchar,getdate(),104)      — Output in dd.mm.yyyy

select convert(varchar,getdate(),105)      — Output in dd-mm-yyyy

select convert(varchar,getdate(),106)      — Output in dd Mon yyyy

select convert(varchar,getdate(),107)      — Output in Mon dd, yyyy

select convert(varchar,getdate(),108)      — Output in hh+:mi:ss

select convert(varchar,getdate(),109)      — Output in Mon dd yyyy hh:mi:ss.mmmXM

select convert(varchar,getdate(),110)      — Output in mm-dd-yyyy

select convert(varchar,getdate(),111)      — Output in yyyy/mm/dd

select convert(varchar,getdate(),112)      — Output in yyyymmdd

select convert(varchar,getdate(),113)      — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(),114)      — Output in hh+:mi:ss:mmm

–Date Conversion formats – specifying Input formats for the varchar dates
select convert(datetime,’12/11/1978′,101)              –Input in mm/dd/ yyyy

select convert(datetime,’1978.12.11′,102)                –Input in yyyy.mm.dd

select convert(datetime,’11/12/1978′,103)              –Input in dd/mm/yyyy

select convert(datetime,’11.12.1978′,104)                –Input in dd.mm.yyyy

select convert(datetime,’11-12-1978′,105)               –Input in dd-mm-yyyy

select convert(datetime,’11 Dec 1978′,106)             –Input in dd-mm-yyyy

select convert(datetime,’Dec 11,1978′,107)             –Input in Mon dd, yyyy

select convert(datetime,’12-11-1978′,110)               –Input in mm-dd-yyyy

select convert(datetime,’1978/12/11′,111)              –Input in yyyy/mm/dd

select convert(datetime,’19781211′,112)  –Input in yyyymmdd

–Date Conversion formats- Input Date format approximation for centuries.
select convert(datetime,’11 Dec 78′,6)      –Input in dd Mon yy

select convert(datetime,’11 Dec 03′,6)      –Input in dd Mon yy

–Date Conversion formats- Input Date format , longest forms and for timestamps .

select convert(datetime,’Dec 11 1978 22:12:12.121′,113)    –Input in Mon dd yyyy hh+:mi:ss:mmm

select convert(datetime,’Dec 11 1978 10:12:12.121PM’,109) –Input in Mon dd yyyy hh:mi:ss.mmmXM

select convert(datetime,’22:12:12.121′,114)             –Input in hh+:mi:ss:mmm

select convert(datetime,’22:12:22′,108)     –Input in hh+:mi:ss

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select * from pubs..employee
where convert(datetime,hire_date,6) > ’11 Dec 93′

select * from pubs..employee where hire_date > ’11 Dec 93′

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select ‘VERIFIED’
where convert(datetime,’11 Dec 93′,6) = convert(datetime,’19931211′,112)

–Date Conversion formats- the ODBC format .
select convert(varchar,hire_date, 121) from pubs..employee where hire_date > ’11 Dec 93′

select substring(convert(varchar,getdate(), 120),12,24)

–Small DateTime format
select getdate(), convert(smalldatetime,getdate( ))

–Days elasped since ‘1.1.1900’
select getdate(),convert(decimal,getdate())

select convert(int,getdate())-convert(int,hire_date) DaysInJob from pubs..employee

–Milliseconds elasped since ‘1.1.1900’

select replace(convert(varchar,getdate(), 114),’:’,”) — stripped time from hh+:mi:ss:mmm

select convert(varchar,getdate(), 112)+replace(convert(varchar,getdate(), 114),’:’,”)

–Unique string generation based on time elasped in milliseconds(?) from absolute zero.
select convert(timestamp,getdate())
go

–Finding the difference in two dates
select datediff(dd,hire_date,getdate()) EmploymentINDays from pubs..employee

select datediff(mm,hire_date,getdate()) EmploymentINMons from pubs..employee

select datediff(yy,hire_date,getdate()) EmploymntINYers from pubs..employee

select datediff(ww,hire_date,getdate()) EmploymntINWeeks from pubs..employee

select datediff(mi,hire_date,getdate()) EmploymntINMinutes from pubs..employee

–Finding Anniversaries in this year
select dateadd(yy,datediff(yy,hire_date,getdate()),hire_date) JOBANNIVERSARY
from pubs..employee
GO
select convert(varchar(6),
dateadd(yy,datediff(yy,hire_date,getdate()),hire_date)) JOBANNIVERSARY
from pubs..employee
GO

–Finding Job Lengths or duration from a fixed past date
select *,datediff(yy,hire_date,getdate()) AS JobYears
from pubs..employee
where datediff(yy,hire_date,getdate()) > 14

–Finding the Year, Month & Date parts from a date expression
select DAY(hire_date) from employee
select MONTH(hire_date) from employee
select YEAR(hire_date) from employee

–Date constituent functions properties
SELECT MONTH(0), DAY(0), YEAR(0)

–DatePart functions
select DATEPART(dw,hire_date) from employee — Day of week when Joining

select DATEPART(dy,hire_date) from employee — Nth Day of Year on Joining

select DATEPART(qq,hire_date) from employee — Quarter of Year at Joining

select DATEPART(mm,hire_date) from employee — Month of Year at Joining

select DATEPART(dd,hire_date) from employee — Day of Month in the year of Joining

#sql-basic