My Favorite Blogs

Saturday, July 17, 2010

SQL Server 2005 - DBMail

Steps to Activate DBMail in SQL Server 2005/2008

1. Enable DBMail XPs

USE [master]
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Database Mail XPs', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

2. Create DBMail Account

USE [master]
GO
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA' ,
@email_address = 'DBMail@
sqldbops.com' ,
@display_name = 'DBMail' ,
@description = 'Mail account for administrative e-mail' ,
@mailserver_name = 'mail.eweb.sqldbops.com' ,
@port = 25
, @use_default_credentials = 1


3. Create DBMail Profile

USE [master]
GO
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DBAProf',
@description=N'Profile used for administrative mail'

4. Add Account to the profile created in step-3

USE [master]
GO
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DBAProf', @account_name=N'DBA', @sequence_number=1

5. Verify if profile created successsfully

SELECT * FROM [msdb].[dbo].[sysmail_profile] as p JOIN [msdb].[dbo].[sysmail_profileaccount] as pa
ON p.profile_id = pa.profile_id

6. Add profile to Public. If you declare as Public, you don't have specify the profile when sending mails using sp_send_dbmail proc. If declared Private, then profile should be specified

EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'DBAProf', @is_default=1

6. Update Registry for SQLServer Agent:(Useful for Cluster env)

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBAProf'

You may not find ‘SQLServerAgent’ key entry on the registry in SQL Server 2005 because registry entries keys totally changed.
But the above query will work successfully(0 rows affected) in SQL Server 2005 and finally enables DBMail for SQL ServerAgent by locating to the following registry(9.0)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\MSSQL.1\SQLServerAgent

7. Verify if Value shows 1 to ensure AgentXps are enabled

USE [master]
GO
select value_in_use from sys.configurations where configuration_id = 16384


8. Create an Operator

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'surpal@sqldbops.com',
@category_name=N'[Uncategorized]'

9. Add Operator to all jobs using the script..

DECLARE @JobName SYSNAME, @job_id UNIQUEIDENTIFIER, @NotifyEmail INT, @SQL NVARCHAR(3000)


SELECT JobName = '%'
DECLARE Job_operator_cursor CURSOR FOR
SELECT name,job_id ,notify_level_email FROM msdb.dbo.sysjobs_view AS sv

OPEN Job_operator_cursor

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name LIKE @JobName)
BEGIN
PRINT 1
SELECT @SQL = 'msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N''DBA'''

PRINT @SQL
EXEC sp_executesql @SQL
END

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
END

CLOSE Job_operator_cursor
DEALLOCATE Job_operator_cursor

10. Restart SQL Server Agent

Steps to De-Activate DBMail in SQL Server 2005/2008

1. Remove Operator from all jobs using the script


DECLARE @JobName SYSNAME, @job_id UNIQUEIDENTIFIER, @NotifyEmail INT, @SQL NVARCHAR(3000)

DECLARE Job_operator_cursor CURSOR FOR
SELECT name,job_id ,notify_level_email FROM msdb.dbo.sysjobs_view AS sv

OPEN Job_operator_cursor

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name = @JobName)
BEGIN
PRINT 1
SELECT @SQL = 'msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'''''

PRINT @SQL
EXEC sp_executesql @SQL
END

FETCH NEXT FROM Job_operator_cursor INTO @JobName, @Job_Id, @NotifyEmail
END

CLOSE Job_operator_cursor
DEALLOCATE Job_operator_cursor


2. Drop DBMail Profile

EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name=N'DBAProf', @force_delete=True


3. Drop DBMail account

EXEC msdb.dbo.sysmail_delete_account_sp @account_name=N'DBA'


4. Remove the entry from DBMail Registry for SQLServer Agent

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''
GO


You may not find ‘SQLServerAgent’ key entry on the registry in SQL Server 2005 because registry entries keys totally changed.
But the above query will work successfully(0 rows affected) in SQL Server 2005 and finally removes DBMailprofile for SQL ServerAgent by locating to the following registry(9.0)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\MSSQL.1\SQLServerAgent

5. Remove Operator from SQL Server Agent

EXEC msdb.dbo.sp_delete_operator @name=N'DBA'

6. Disable DBMail XPs

USE [master]
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'Database Mail XPs', N'0'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO






No comments:

Post a Comment