October 7, 2008 at 9:45 am
DECLARE @body nvarchar(max)
SET @body =
'The system has detected the following event: '
'SQL Alert Notification: No Activity '
'Date time:' @TimeStamp
'AlertNotified ' @AlertNotified
'Resource ' @Resource
'--'
'--Description: '
'--The time the last transaction has taken place has exceeeded 10 minutes'
can some please help me use escape characters make my email body text look like the above text
October 7, 2008 at 9:56 am
[font="Courier New"]
SET @body =
'The system has detected the following event: ' + CHAR(10) + CHAR(13) +
'SQL Alert Notification: No Activity ' + CHAR(10) + CHAR(13) +
'Date time:' + CAST(@TimeStamp AS VARCHAR(50)) + CHAR(10) + CHAR(13) +
'AlertNotified ' + CAST(@AlertNotified AS VARCHAR(50)) + CHAR(10) + CHAR(13) +
'Resource ' + CAST(@Resource AS VARCHAR(50)) + CHAR(10) + CHAR(13) +
'--' + CHAR(10) + CHAR(13) +
'--Description: ' + CHAR(10) + CHAR(13) +
'--The time the last transaction has taken place has exceeeded 10 minutes' + CHAR(10) + CHAR(10)
[/font]
This will not look correct in Query Analyzer, but should return in the proper format to the displaying application... depending on what that app is.
If you get a NULL result, it's because Timestamp, alertnotified or resource was NULL. Wrap those variables in ISNULL(x,'Unkown')'s to avoid this.
October 7, 2008 at 3:00 pm
Garadin's post points out why I like to put the Carriage Return/Line Feed into a variable constant rather than repeating it over and over. His last one is incorrect 🙂
DECLARE @body varchar(max), @CRLF char(2)
SET @CRLF = char(13) + char(10) -- I can never remember the order but I believe this is correct...
SET @body =
'The system has detected the following event: ' + @CRLF +
'SQL Alert Notification: No Activity ' + @CRLF +
'Date time:' + CAST(@TimeStamp AS VARCHAR(50)) + @CRLF +
'AlertNotified ' + CAST(@AlertNotified AS VARCHAR(50)) + @CRLF +
'Resource ' + CAST(@Resource AS VARCHAR(50)) + @CRLF +
'--' + @CRLF +
'--Description: ' + @CRLF +
'--The time the last transaction has taken place has exceeeded 10 minutes' + @CRLF
Gary Johnson
Sr Database Engineer
October 7, 2008 at 3:31 pm
Why bother with CR+LF at all? This isn't VB. Just leave them in the string an be done with it. This works with many targets:
SELECT @body = 'The system has detected the following event:
SQL Alert Notification: No Activity
Date time:' + @TimeStamp + '
AlertNotified ' + @AlertNotified + '
Resource ' + @Resource + '
--
--Description:
--The time the last transaction has taken place has exceeeded 10 minutes'
-Eddie
Eddie Wuerch
MCM: SQL
October 8, 2008 at 1:04 am
thanks guys
October 8, 2008 at 5:41 am
Garadin's post points out why I like to put the Carriage Return/Line Feed into a variable constant rather than repeating it over and over. His last one is incorrect.
Good call. I'm not sure why I never decided to store those to a variable myself.
Why bother with CR+LF at all? This isn't VB. Just leave them in the string an be done with it. This works with many targets:
It works... but it doesn't format the output. Sometimes you need SQL to format the output for you, for things such as xp_sendmail, and this gives you the ability to do it. I'd much rather read an email that looked something like:
Employee: Bob Jones
EMPID: 123456
Phone: (123) 456 - 7890
Address: 123 Somewhere Street
Someplace, FL 12345
Schedule
----------
Monday: 8-5
Tuesday: 8-5
Wednesday: 10-7
Thursday: 8-5
Friday: 9-6
Than this:
Employee: Bob Jones EmpID: 12345 Phone: (123) 456 - 789 Address: 123 Somewhere Street
Someplace, FL 12345 Schedule: Monday: 8-5 Tuesday: 8-5 Wednesday: 10-7 Thursday: 8-5 Friday: 9-6
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply