Archive

Archive for September, 2010

Database Mail Setup – SQL Server 2005

September 29, 2010 12 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
go
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure

Initial Cleanup:

IF EXISTS(
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
      p.name = @ProfileName AND
      a.name = @AccountName)
BEGIN
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp
      @profile_name = @ProfileName,
      @account_name = @AccountName
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName)
BEGIN
      PRINT 'Deleting Profile.'
      EXECUTE sysmail_delete_profile_sp
      @profile_name = @ProfileName
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName)
BEGIN
      PRINT 'Deleting Account.'
      EXECUTE sysmail_delete_account_sp
      @account_name = @AccountName
END

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 = 'abc@xyz.com',
    @replyto_address = 'abc@xyz.com',
    @display_name = 'Manoj Pandey',
    @mailserver_name = 'smtp.xxxx.net',
	@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
	@recipients=N'manub22@gmail.com',
	@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 = 'GroupSQLDBA@MyCo.com'
    ,@from_address = 'DBMail@MyCo.com'
    ,@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:

--Profiles
SELECT * FROM msdb.dbo.sysmail_profile

--Accounts
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',
       fail.mailitem_id,
       LOG.description
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.

Above stuff is a collation form the following related links, just for reference:
http://msdn.microsoft.com/en-us/library/ms175887(v=SQL.90).aspx
http://articles.techrepublic.com.com/5100-10878_11-6161839.html#

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: http://www.red-gate.com/specials/Ebooks/Defensive_Database_Programming.pdf
Related article: http://www.simple-talk.com/sql/t-sql-programming/book-review-defensive-database-programming-with-sql-server/

Combine multiple ROWS to CSV String… and vice-versa

September 16, 2010 12 comments

There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String.
Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below.

It was a bit difficult to do in SQL Server 2000 and requires to create a customized function or a script.
But with SQL Server’s 2005 version, the new “PATH” mode with “FOR XML” provides us a way to get the desired output. Its a bit tricky but will do the needful, that’s why I’ve also added this post for my reference as well.

CREATE TABLE #tempCityState (State VARCHAR(5), City VARCHAR(50))

INSERT INTO #tempCityState
SELECT 'CO', 'Denver' UNION
SELECT 'CO', 'Teluride' UNION
SELECT 'CO', 'Vail' UNION
SELECT 'CO', 'Aspen' UNION
SELECT 'CA', 'Los Anggeles' UNION
SELECT 'CA', 'Hanford' UNION
SELECT 'CA', 'Fremont' UNION
SELECT 'AK', 'Wynne' UNION
SELECT 'AK', 'Nashville'

SELECT * FROM #tempCityState
State       City
AK          Nashville
AK          Wynne
CA          Fremont
CA          Hanford
CA          Los Anggeles
CO          Aspen
CO          Denver
CO          Teluride
CO          Vail

Using the “FOR XML PATH” syntax:

SELECT DISTINCT State, (SELECT SUBSTRING((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),2,200000)) AS Cities
INTO #tempCityStateCSV
FROM #tempCityState t

-- OR --

SELECT DISTINCT State, (SELECT STUFF((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),1,1,'')) AS Cities
FROM #tempCityState t

SELECT * FROM #tempCityStateCSV
State       Cities
AK          Nashville,Wynne
CA          Fremont,Hanford,Los Anggeles
CO          Aspen,Denver,Teluride,Vail

Now, converting it back to multiple rows:

SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City
FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String
	FROM  #tempCityStateCSV) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
ORDER BY 1,2
State       City
AK          Nashville
AK          Wynne
CA          Fremont
CA          Hanford
CA          Los Anggeles
CO          Aspen
CO          Denver
CO          Teluride
CO          Vail

Final Cleanup:

DROP TABLE #tempCityStateCSV
DROP TABLE #tempCityState
Follow

Get every new post delivered to your Inbox.

Join 411 other followers