-1

So, for SQL 2008 R2 or below, when looking in:

  • SQL Server Agent Properties
    • Alert System
      • Mail System

I see SQLMail (Urrggghh) and Database Mail (hurrah)

When I use msdb.dbo.sp_set_sqlagent_properties against SQL 2008 R2, I cannot use the @use_databasemail parameter as this is only valid from SQL 2012.

So, the question is, do any of you know how to set the Mail System to use Database Mail using T-SQL? I’ve tried and failed!

I really don’t want to have to do this manually as the scripts I’ve written will be going out to field engineers. Everything else works like a dream, except for this!

I thought of using the code below BUT, this caters for default instances, not named instances:

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

Any help would be appreciated.

0

So, after a bit more work i realised that the below code does in fact work as its based on the connection / instance. My code takes into account the sql version number.

USE [msdb];
GO

DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));
--Do this if the sql version is SQL 2008R2 or below
IF left(@ProductVersion,2) <11
     --Set the sql agent to use Database Mail and not SQLMail
     EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1;
IF left(@ProductVersion,2) <11
     --Set sql agent to use the 'MyDBMailProfile' mail profile
     EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'MyDBMailProfile;

ELSE
--Do this if the sql version is above SQL 2008R2
 EXEC msdb.dbo.sp_set_sqlagent_properties 
        @databasemail_profile=N'MyDBMailProfile', 
        @use_databasemail=1;
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.