Standardize SQL2005 Alerts using sysmail SP's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Standardize SQL2005 Alerts using sysmail SP's

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2008-11-25 : 11:59:03

I am trying to standardize my shop's approach to email alerts
by creating a script that sets up Database Mail using
parameterized stored procedures. Predefining parameter values works fine
for these :

sp_add_operator ,
sysmail_add_profile_sp ,
and
sysmail_add_account_sp

but it fails for sysmail_add_profileaccount_sp. I get a
datatype conversion error. I don't see where the conversion is attempted.

Here are 3 examples of this command. #1 works, but I don't want to
hard-code the values. I want to preset the values.

--===============================================
-- #1 This works but I DON'T WANT TO DO THIS.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyServerSQL2005AlertProfile' ,
@account_name = 'MyServerDBSAlertAccount' ,
@sequence_number = 1 ;

--================================================
-- #2 This Fails

declare @profile_name sysname
declare @account_name sysname
declare @sequence_number int
set @sequence_number = 1
set @account_name = 'MyServerDBSAlertAccount'
set @profile_name = 'MyServerSQL2005AlertProfile'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name ,
@account_name ,
@sequence_number ;

ERROR MESSAGE: Msg 8114, Level 16, State 1, Procedure sysmail_add_profileaccount_sp, Line 0
Error converting data type nvarchar to int.

--================================================
-- #3 This Fails

declare @profile_name sysname
declare @account_name sysname
set @account_name = 'MyServerDBSAlertAccount'
set @profile_name = 'MyServerSQL2005AlertProfile'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name ,
@account_name ,
@sequence_number = 1 ;

ERROR MESSAGE: Msg 8114, Level 16, State 1, Procedure sysmail_add_profileaccount_sp, Line 0
Error converting data type nvarchar to int.
--===========================================

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 13:36:04

What are trying to alert ? Didn't get it?

dbthj
Posting Yak Master

143 Posts

Posted - 2008-11-25 : 14:11:39

I am setting up the
operator
profile
account
put the account in the profile
set the profile to default

All with standard names. That means a script.
That means use the sysmail SP's and set the parameter
values without using literals.

Then I can set up jobs and maintenance plans to send email alerts to the
DBAs when things go kabloooey.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources