Adding SQL Server Agent Operators w/o GUI

Question

Adding SQL Server Agent Operators w/o GUI

SQLMD

Right there with Babe

Points: 764
More actions
November 19, 2010 at 9:23 am

#149408

We recently decided to start using DBMail on our SQL servers mostly to notify for failed SQL Agent jobs. After setting up the first couple manually I decided that for the remaining servers I really needed to script it. We only have about 10 instances, but we are adding more all the time and it just made sense to have an easy way to add our configuration to a server. I was able to create a TSQL script for the DBMail portion pretty easily since there is a template for this, but I am unable to find the right sp's for adding operators and for enabling the Mail session in the SQL Server Agent. Does anyone know of a way to do this with TSQL? Or has anyone done this using Powershell? I'm still figuring out all that Powershell can do and figured I would dive in on that next, but didn't want to reinvent the wheel. Thanks.
-Mike

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLMD Right there with Babe Points: 764 More actions · Answer 1

So I guess I should have looked a little longer before posting. I have figured out how to add the Operator (sp_add_operator), but i am still looking for the TSQL to enable the Mail profile in the Alert System for SQL Agent.

Adiga One Orange Chip Points: 27284 More actions · Answer 2

This should work.

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

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SQLMD Right there with Babe Points: 764 More actions · Answer 3

Adiga (11/19/2010)
This should work.
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'MailProfileName'

Excellent! I may still look at Powershell to do something with this as I have to loop through all of the Jobs to change the notify settings, but I could do that in TSQL too if I wanted.

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 4

I can't find the site reference to the one I used when writing this but here's a code snippet from something I use to set up new servers using dynamic SQL. The registry paths are calculated in a previous step so you will have to replace these with what you need:

-- Enable the Database Mail profile in SQL Server Agent’s Alert System

SELECT

@RegKeyPath= @RegBasePath + '\SQLServerAgent'

,@RegKey= 'DatabaseMailProfile'

EXEC @rc = master.dbo.xp_regread @RegRoot, @RegKeyPath, @RegKey, @RegDatabaseMailProfile OUTPUT, 'no_output'

IF ( ISNULL(@RegDatabaseMailProfile, '') <> 'Default Profile' )

BEGIN

SELECT @Message = ' Enabling ''Default Profile'' mail profile in SQL Server Agent'

PRINT(@Message)

SELECT @TSQL = ' EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1'

PRINT(@TSQL)

IF ( @ReportOnly = 0 ) EXEC(@TSQL)

SELECT @TSQL = ' EXEC master.dbo.xp_instance_regwrite N''' + @RegRoot + ''', N''' + @RegKeyPath + ''', N''UseDatabaseMail'', N''REG_DWORD'', 1'

PRINT(@TSQL)

IF ( @ReportOnly = 0 ) EXEC(@TSQL)

SELECT @TSQL = ' EXEC master..xp_regwrite @rootkey=N''' + @RegRoot + ''', @key=N''' + @RegKeyPath + ''', @value_name=N''' + @RegKey + ''', @type=N''REG_SZ'', @value=''Default Profile'''

PRINT(@TSQL)

IF ( @ReportOnly = 0 ) EXEC(@TSQL)

END