October 25, 2006 at 1:37 pm
I have a job set up that sends SMTP mail to the DBA Oncall when certain errors occur on the server. It works fine most of the time, but if the [A-MSG] token contains single quotes it was failing. I modified the job to take care of that issue with the below job, but when I was testing I tried to send a message with double quote and it fails with syntax error. Would the [A-MSG] token ever contain a double quote. If so, how can I change the job to take both single quote and double quotes into account? I tried
REPLACE(REPLACE('[A-MSG]','"',''),'''','') and
REPLACE(REPLACE("[A-MSG]",'"',''),'''','')
but that either takes care of one or the other not both. How can I have the job be able to take care of either a single or double quote or both in the [A-MSG] token?
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.usp_send_cdosysmail
@From = 'fromaddress',
@To = 'toaddress',
@Subject = 'SQL Server Alert - error [A-ERR] occurred on [SRVR]',
@Body = @Msg
TIA,
Michelle
October 25, 2006 at 10:41 pm
October 26, 2006 at 5:43 am
Yes, I tried that also, but that fails with invalid column name 'A-MSG'.
October 26, 2006 at 6:02 am
with 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:[A-MSG]' + 'Check the [SRVR] SQL Server ErrorLog and the Application '+ 'event log on the server for additional details'
you only get a string. No columns or data from elsewhere is used !
I guess you'd be better off whith building your "old" message, and the just perform a replace :
Set @Msg = replace(@Msg, '''', '')
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply