Archive for September, 2010

SQL DBA – Database Mail Setup – SQL Server 2005

September 29, 2010 14 comments

Database Mail, a new addition to the SQL Server 2005 database engine, is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

Database Mail provides a robust, high-performance replacement for the most commonly requested features of SQL Mail. Database Mail is designed to operate with SMTP servers, and is tested with Microsoft SMTP servers.

Note: Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the “SQL Server Surface Area Configuration” tool or the “Database Mail Configuration Wizard“.

Database Mail Setup:

Enabling the Database Mail feature: By default the Database mail Stored-Procs are disabled.

For Express Edition, check the required files exists in MSSQL\Binn folder. If not then copy the following 3 files:
1. DatabaseMail90.exe
2. DatabaseMailEngine.dll
3. DatabaseMailProtocols.dll

Then execute the following Stored-Porcs:

exec dbo.sysmail_start_sp
exec dbo.sysmail_stop_sp


A User must be a member of the DatabaseMailUserRole database role in the msdb database.

To Enable Database Mail execute the following block of code:

use master
exec sp_configure 'show advanced options', 1
exec sp_configure 'Database Mail XPs', 1


Initial Cleanup:

SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE = @ProfileName AND = @AccountName)
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp
      @profile_name = @ProfileName,
      @account_name = @AccountName

SELECT * FROM msdb.dbo.sysmail_profile p
WHERE = @ProfileName)
      PRINT 'Deleting Profile.'
      EXECUTE sysmail_delete_profile_sp
      @profile_name = @ProfileName

SELECT * FROM msdb.dbo.sysmail_account a
WHERE = @AccountName)
      PRINT 'Deleting Account.'
      EXECUTE sysmail_delete_account_sp
      @account_name = @AccountName


Setting up Accounts & Profiles for DB Mail:
Create a Database Mail account for holding information about an SMTP account.
An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.

--// Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Test Mail Account',
    @description = 'Mail account for administrative e-mail.',
    @email_address = '',
    @replyto_address = '',
    @display_name = 'Manoj Pandey',
    @mailserver_name = '',
	@port = 587,
	@username = 'xyz',
	@password = 'xxyyzz',
	@enable_ssl = 1

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Test Mail Profile',
    @description = 'Profile used for administrative mail.'

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Test Mail Profile',
    @account_name = 'Test Mail Account',
    @sequence_number =1

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Test Mail Profile',
    @principal_name = 'public',
    @is_default = 1


Sending Mail and Check its status:

--Send mail
EXEC msdb.dbo.sp_send_dbmail
	@body= 'Test Email Body',
	@subject = 'Test Email Subject',
	@profile_name = 'Test Mail Profile'
--Send mail with attachment
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'DBMail'
    ,@recipients = ''
    ,@from_address = ''
    ,@query = 'SELECT resource_type, resource_database_id,
                    request_mode, request_session_id
               FROM sys.dm_tran_locks
              WHERE request_mode IN (''IX'', ''X'')'
    ,@subject = 'Exclusive Locks'
    ,@attach_query_result_as_file = 1 ;


Important Tables used in configuring Database mail and check their status:

SELECT * FROM msdb.dbo.sysmail_profile

SELECT * FROM msdb.dbo.sysmail_account

--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount

--Principal Profile
select * from msdb.dbo.sysmail_principalprofile

--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration

--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems

--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
FROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail
ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error'

--Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp

--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp


Note: After performing above steps the mails were not getting sent. I was configuring DB mail from my client machine, even with the admin account. I was getting various errors like:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 19 (2010-10-22T07:24:21). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond IP_ADD:25).

Then I tried to setup the DB mail from the server itself and it worked in one go.


difference between COUNT() and COUNT_BIG() – MSDN TSQL forum

September 28, 2010 Leave a comment

–> Question:

What is the difference between COUNT() and COUNT_BIG() in SQL Server ?

–> Shaahs Answer:

COUNT() and COUNT_BIG() are similar in functionality, but COUNT() always returns INT and COUNT_BIG() returns BIG INT.

MSDN reference

–> My Answer:

Also COUNT() can not be used while creating Indexed Views.

And COUNT_BIG() is a mandatory requirement to include in Indexed Views.

Include other columns while doing aggregates and GROUP BY – MSDN TSQL forum

September 22, 2010 Leave a comment

–> Question:

I need maximum mark scored each students and min mark scored each students.

I have done this way…

select ID, max(mark), min(mark) 
from table1 
group by ID

But i need more field as Class…. I don’t want include class field in group by clause

–> My Answer:

Try joining the query with the table, like:

select T1.*, T2.ID, T2.max_mark, T2.min_mark
from table1 T1
join (select ID,max(mark) as max_mark,min(mark) as min_mark
	from table1 
	group by ID
) AS T2
on T1.ID=T2.ID


–> shaahs Answer:

In this situation you can use the window functions:

select ID, Class, 
	max(mark) over(partition by ID), 
	min(mark) over(Partition by ID) 
from table1

… in this cause you don’t want to include the fields in group by clause.

Defensive Database Programming with SQL Server… a must have (e)book

September 21, 2010 Leave a comment

Book By Alex Kuznetsov… and a must read for all T-SQL techies.

The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected.
To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn’t break under concurrent loads, and survives predictable changes to database schemas and settings.
Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality of your T-SQL code and increase its resilience and robustness.

Download the e-book from here:
Related article:

Stopping a DELETE statement in middle of its execution – MSDN TSQL forum

September 17, 2010 Leave a comment

–> Question:

I accidentally executed the DELETE statement for a table.

However, I stopped it before it completed.

What effect will this have on the table in Database, is my data safe?

–> My Answer:

In SQL Server by default a single SQL statement is bounded within a Transaction.

So, if you cancelled the DELETE statement execution in between it would have cancelled the overall deletion from the table. So either all of your rows are safe or all gone. SQL Server ensures that a transaction should be completed fully or not at all, one of the ACID properties.