Db Maint SMTP Alerter Question

I'm in the process of installing and configuring alerts using the
subject tool on our sql server 2000 instances but I've noticed a problem
with the tool and single-quotes. It seems any alert which returns a
single-quoted value in the message text generates incorrect code when
trying to execute xp_smtp_sendmail. For instance, as a test I created
an alert on error 208, Invalid object name '%.*ls'. Here is the code
that is created by Db SMTP Alerter:
EXEC master..xp_smtp_sendmail
@.FROM = 'eat@.joes.com'
,@.FROM_NAME = 'eat'
,@.priority = 'NORMAL'
,@.server = 'smtp.server.com'
,@.port = 25
,@.TO = 'eat@.joes.com'
,@.subject = 'Db Maint alerter: ''Test Alert'' occurred on \\SERVER.'
,@.message = 'SQL Server error text:
====================== Error: 208, Severity: 16, State: 1
Invalid object name 'TestObject'.
'
In order for the code to execute correctly, the 'TestObject' in @.message
should be ''TestObject''. That is, there should be double
single-quotes, not simply single-quotes. The ''Test Alert'' in @.subject
is correct, but throwing an apostrophe into the alert name will also
screw things up (ie ''Test'd Alert'' . Has anyone else using Db Maint
SMTP Alerter encounter this issue? The alert name issue is easy to
avoid, but there are several alerts I'd like to use that return
single-quoted values.I just checked the source code, and I do have a REPLACE for the message text. Perhaps you are using
an older version? Try downloading it from http://www.karaszi.com/SQLServer/util_smtp_alerter.asp and
see if that fixes it. If not, send me an e-mail and I can let you have the source code for fixing (I
don't have the resources to convert to FX 2.0, debug etc etc, I'm afraid).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DotComDBA" <noreply@.nospam.com> wrote in message news:O9VBgmLwGHA.5044@.TK2MSFTNGP05.phx.gbl...
> I'm in the process of installing and configuring alerts using the subject tool on our sql server
> 2000 instances but I've noticed a problem with the tool and single-quotes. It seems any alert
> which returns a single-quoted value in the message text generates incorrect code when trying to
> execute xp_smtp_sendmail. For instance, as a test I created an alert on error 208, Invalid object
> name '%.*ls'. Here is the code that is created by Db SMTP Alerter:
> EXEC master..xp_smtp_sendmail
> @.FROM = 'eat@.joes.com'
> ,@.FROM_NAME = 'eat'
> ,@.priority = 'NORMAL'
> ,@.server = 'smtp.server.com'
> ,@.port = 25
> ,@.TO = 'eat@.joes.com'
> ,@.subject = 'Db Maint alerter: ''Test Alert'' occurred on \\SERVER.'
> ,@.message = 'SQL Server error text:
> ======================> Error: 208, Severity: 16, State: 1
> Invalid object name 'TestObject'.
> '
> In order for the code to execute correctly, the 'TestObject' in @.message should be ''TestObject''.
> That is, there should be double single-quotes, not simply single-quotes. The ''Test Alert'' in
> @.subject is correct, but throwing an apostrophe into the alert name will also screw things up (ie
> ''Test'd Alert'' . Has anyone else using Db Maint SMTP Alerter encounter this issue? The alert
> name issue is easy to avoid, but there are several alerts I'd like to use that return
> single-quoted values.|||Tibor Karaszi wrote:
> I just checked the source code, and I do have a REPLACE for the message
> text. Perhaps you are using an older version? Try downloading it from
> http://www.karaszi.com/SQLServer/util_smtp_alerter.asp and see if that
> fixes it. If not, send me an e-mail and I can let you have the source
> code for fixing (I don't have the resources to convert to FX 2.0, debug
> etc etc, I'm afraid).
>
The current version works! Looks like I was using an older version
(downloaded from the dbmaint.com page). Thanks, Tibor!|||Glad it works. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DotComDBA" <noreply@.nospam.com> wrote in message news:uz6oioVwGHA.2232@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>> I just checked the source code, and I do have a REPLACE for the message
>> text. Perhaps you are using an older version? Try downloading it from
>> http://www.karaszi.com/SQLServer/util_smtp_alerter.asp and see if that
>> fixes it. If not, send me an e-mail and I can let you have the source
>> code for fixing (I don't have the resources to convert to FX 2.0, debug
>> etc etc, I'm afraid).
> The current version works! Looks like I was using an older version
> (downloaded from the dbmaint.com page). Thanks, Tibor!