Using links in @body with xp_sendmail

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 2000 Forums

SQL Server Development (2000)

Using links in @body with xp_sendmail

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-08-06 : 11:25:15

I have created the following stored procedure that runs as a job on our SQL server. It sends out an email if the criteria is met. The email contains a link for the recipient so they may get to the details of their request. This link works fine. What I'd like to do is add an email link for the scripter name in the last sentence of the email, where it says "contact your scripter". I can't seem to get it to work. Any suggestions? Thanks!

DECLARE
@RequestId int,
@HistDate smalldatetime,
@RequestType char(1),
@RequesterName varchar(60),
@RequesterEmail varchar(60),
@ScripterEmail varchar(60),
@ScripterName varchar(60),
@BusownerEmail varchar(60),
@TechownerEmail varchar(60),
@ExpertTesterEmail varchar(60)

DECLARE ReqCursor CURSOR FOR
SELECT DISTINCT
RSH.Request_ID,
MAX(RSH.Date) AS HistDate,
R.Request_Type,
R.Requester_Name,
R.Requester_Email,
RSD.Scripter_Name,
RSD.Scripter_Email,
RS.Busowner_Email,
RS.Techowner_Email,
RS.ExpertTester_Email
FROM
ReqStatusHist RSH
LEFT OUTER JOIN
ReqCommon R ON RSH.Status_ID = R.Current_Status_ID
LEFT OUTER JOIN
ReqScripterDef RSD ON R.Scripter_ID = RSD.Scripter_ID
LEFT OUTER JOIN
ReqScripting RS ON R.Request_ID = RS.Request_ID
WHERE
(RSH.Status_ID = 9) AND
(DATEDIFF(day, RSH.Date, getdate()) >= 10) AND
(R.NotifiedWaiting = 0) AND
(R.Request_Type = 'S')
GROUP BY
RSH.Request_ID,
R.Requester_Name,
R.Requester_Email,
RSD.Scripter_Name,
RSD.Scripter_Email,
RS.Busowner_Email,
RS.Techowner_Email,
RS.ExpertTester_Email,
R.Request_Type

OPEN ReqCursor
FETCH NEXT FROM ReqCursor
INTO
@RequestID,
@HistDate,
@RequestType,
@RequesterName,
@RequesterEmail,
@ScripterName,
@ScripterEmail,
@BusownerEmail,
@TechownerEmail,
@ExpertTesterEmail

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
EXEC master..xp_startmail
DECLARE @Body varchar(2000)
SET @Body='Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(13) + CHAR(13) + 'The request listed above has reached 10 days of Waiting on Expert Testing. As a courtesy, we are notifying you so that you may take proper action to identify possible issues with expert testing. For a detailed report on your request, please click on the following link: (http://dboas.bpweb.bp.com/StatusDetailScripting.asp?request_id=' + CAST(@RequestID as varchar(10)) + '). For more information regarding your request, please contact your scripter. Thank you.'
DECLARE @AllRecipients varchar(255)
SET @AllRecipients = @RequesterEmail + ';' + @ScripterEmail + ';' + @BusownerEMail + ';' + @TechownerEmail + ';' + @ExpertTesterEmail
EXEC master..xp_sendmail @recipients=@AllRecipients, @subject='ACTION REQUIRED: Waiting on Expert Testing', @dbuse='testing', @message=@Body

UPDATE ReqCommon
SET NotifiedWaiting = 1
WHERE Request_ID = @RequestID
EXEC master..xp_stopmail
END
FETCH NEXT FROM ReqCursor
INTO
@RequestID,
@HistDate,
@RequestType,
@RequesterName,
@RequesterEmail,
@ScripterName,
@ScripterEmail,
@BusownerEmail,
@TechownerEmail,
@ExpertTesterEmail
END

CLOSE ReqCursor
DEALLOCATE ReqCursor

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources