SMTP mail to alert of DB errors

Question

SMTP mail to alert of DB errors

Michelle Cook

SSCrazy

Points: 2119
More actions
August 3, 2005 at 11:48 am

#94386

We are currently using a stored procedure which executes SMTP mail to
notify us when errors occur in our SQL Server instances. We setup the alerts
for errors with severity > 20 and other things like replication errors to
execute a job that executes this stored procedure. It has been working
fine, but occassionally fails. One failure of the job was today when our
snapshot replication failed. The error that the snapshot replication
received was similar to:
Replication-Replication Distribution Subsystem: agent svrname\instname
-DBNAME-servername\instanme-14 failed. The process could not bulk copy
into table '"prod_table"'..
The alert triggered successfully and executed the job that runs the
SMTP mail stored procedure but the job failed with:
Executed as user: domain\username. Line 10: Incorrect syntax
near 'customer_branch_carrier'. [SQLSTATE 42000] (Error 170).
The step failed.
The job executes this step:
EXECUTE master.dbo.usp_send_smtpmail
@From = 'username@org.com',
@To = 'critical.sql@org.com',
@subject = 'SQL Server Alert - error [A-ERR] occurred on [SRVR]',
@Body = "Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG]
Check the [SRVR] SQL Server ErrorLog and the Application event log on the server for additional details"
I understand why the job failed because of the single, then double quotes
around the table name in the error message ([A-MSG]). My question is how can I execute the SMTP mail stored procedure to workaround that. I was thinking to just remove the Message: [A-MSG] part in the body of the e-mail, but the addtional information sometimes comes in handy. Any ideas?
TIA.........Michelle

Viewing 9 posts - 1 through 8 (of 8 total)

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

Came across this one myself. What you'll need to do is assign the [A-MSG] token to a variable and then replace the quotes.

EG: From memory you end up with something like this.

DECLARE @Msg varchar(500)

SET @Msg = 'Error: [A-ERR]' + CHAR(10) + 'Severity: [A-SEV]' + CHAR(10) 
+ 'Date: [STRTDT]' + CHAR(10) + 'Time: [STRTTM]' + CHAR(10) 
+ 'Database: [A-DBN]' + CHAR(10) + Message: ' + REPLACE([A-MSG], '''', '')
+ 'Check the [SRVR] SQL Server ErrorLog and the Application '
+ 'event log on the server for additional details'

EXECUTE master.dbo.usp_send_smtpmail
@From = 'username@org.com', 
@To = 'critical.sql@org.com',
@subject = 'SQL Server Alert - error [A-ERR] occurred on [SRVR]',
@Body = @Msg

--------------------
Colt 45 - the original point and click interface

Michelle Cook SSCrazy Points: 2119 More actions · Answer 2

Thanks for the advice Phill. I'll give it a try.

Michelle

Michelle Cook SSCrazy Points: 2119 More actions · Answer 3

Phil,

Can you give me an example of how to raiseerror to test the modification to the job?

TIA....Michelle

philcart SSC-Forever Points: 47794 More actions · Answer 4

You could try something like the following in query Analyzer. Despite appearences, only single quotes are used. Also, make sure you have an alert setup for the severity level, or change the severity level in the RAISERROR statement.

RAISERROR('testing ''quoted text'' inside an alert', 16,1)

--------------------
Colt 45 - the original point and click interface

Michelle Cook SSCrazy Points: 2119 More actions · Answer 5

Phill,

I changed my job to alert on replication 14151 errors as you suggested to execute this step:

declare @Msg varchar(4000)

SET @Msg = 'Error: [A-ERR]' + CHAR(10) + 'Severity: [A-SEV]' + CHAR(10) + 'Date: [STRTDT]' + CHAR(10) + 'Time: [STRTTM]' + CHAR(10) + 'Database: [A-DBN]' + CHAR(10) + 'Message: REPLACE([A-MSG], '''', '')' + 'Check the [SRVR] SQL Server ErrorLog and the Application '+ 'event log on the server for additional details'

EXECUTE master.dbo.sp_send_cdontsmail

@From = 'dbadmin@ups-scs.com',

@To = 'critical.sql@ups-scs.com',

@subject = 'SQL Server Alert - error [A-ERR] occurred on [SRVR]',

@Body = @Msg

The job still fails though when the [A-MSG] has a single quote and then double quotes around the table name. This is the error in the SQL Server log:

Replication-Replication Distribution Subsystem: agent....failed. The process could not bulk copy into table '"batch_details"'..

The error triggered the alert which executed my job, but the job failed with:

Executed as user: TG-MASTER\oemmgr. Line 2: Incorrect syntax near 'batch_details'. [SQLSTATE 42000] (Error 170). The step failed.

The replace doesn't seem to be fixing the problem. Any ideas?

TIA......Michelle

Michelle Cook SSCrazy Points: 2119 More actions · Answer 6

Phill,

I think the problem is I had my @Msg variable wrong. I had it as:

SET @Msg = 'Error: [A-ERR]' + CHAR(10) + 'Severity: [A-SEV]' + CHAR(10) + 'Date: [STRTDT]' + CHAR(10) + 'Time: [STRTTM]' + CHAR(10) + 'Database: [A-DBN]' + CHAR(10) + 'Message: REPLACE([A-MSG], '''', '')' + 'Check the [SRVR] SQL Server ErrorLog and the Application '+ 'event log on the server for additional details'

and I think it should be:

SET @Msg = 'Error: [A-ERR]' + CHAR(10) + 'Severity: [A-SEV]' + CHAR(10) + 'Date: [STRTDT]' + CHAR(10) + 'Time: [STRTTM]' + CHAR(10) + 'Database: [A-DBN]' + CHAR(10) + 'Message:' + REPLACE([A-MSG], '''', '') + 'Check the [SRVR] SQL Server ErrorLog and the Application '+ 'event log on the server for additional details'

Is that right? If so, when I go to change my job step I get a pop-up window stating: Invalid column name 'A-MSG' Will that cause it to fail?

Thanks,

Michelle

philcart SSC-Forever Points: 47794 More actions · Answer 7

Michelle, Try this,

REPLACE("[A-MSG]", '''','')

--------------------
Colt 45 - the original point and click interface

Michelle Cook SSCrazy Points: 2119 More actions · Answer 8

Phil,

Thanks for your help. I think that it works now. The true test will be the next time the replication fails. I guess now I have to figure out why the replication is failing in the first place

Thanks again,

Michelle