asked on

Hyperlinks in msdb.dbo.sp_send_dbmail

Is it possible to wrap a hyperlink on to one of the output lines in the following CAST section of a dbmail

The hyperlink I am trying to recreate on the email looks like this in an ordinary .asp page.
<a target="_blank" href="time/printemployeetimesheet.asp?WorkDate=<%=RSTempTimeEntries("DateOfWork")%>&EmployeeName=<%=RSTempTimeEntries("LastFirstName")%>">TS</a>

except that the variables <%=RSTempTimeEntries("DateOfWork")%>
and <%=RSTempTimeEntries("LastFirstName")%> would be the "td = DateOfWork" and "td = LastFirstName" values from the CAST statement.

declare @NewSubjectLine varchar(200)
declare @StartDateLine varchar(11)
declare @EndDateLine varchar(11)
declare NoticeRecipients varchar(2000)
set @StartDateLine=convert(varchar(11),@StartDate)
set @EndDateLine=convert(varchar(11),@EndDate)
set @NewSubjectLine='Possible Duplicated Timesheet Entries from '+@StartDateLine+' to '+@EndDateLine
set NoticeRecipients='nobody@whoknows.com'
 
SET @tableHTML =
    N'<H1>Possible Duplicated Timesheet Entries from '+@StartDateLine+' to '+@EndDateLine+'</H1>' +
    N'<table border="1">' +
    N'<tr><th>EmployeeID</th>' +
	N'<th>Employee Name</th>' +
    N'<th>Date Of Work</th>' +
    N'<th>Timesheet Hours</th>' +
    N'<th>Job Numbers</th>' +
    N'<th>Daywork Numbers</th></tr>' +
 
    CAST ( ( SELECT td = EmployeeID,       '',
                    td = LastFirstName, '',
                    td = convert(varchar(11),DateOfWork), '',
                    td = convert(varchar(10),Hours), '',
                    td = JobNumbers, '',
                    td = DayworkNumbers
              FROM #Entries 
			  order by DateOfWork,FirstLastName
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' +
N'  '+
N'<H1>Please verify these entries and correct any errors</H1>';
EXEC msdb.dbo.sp_send_dbmail @recipients=@NoticeRecipients,
@profile_name = 'HRNotice',
@subject=@NewSubjectLine ,
@body = @tableHTML,
@body_format = 'HTML' ;

Open in new window

reb73

You mean like this?

SET @tableHTML =
    N'<H1>Possible Duplicated Timesheet Entries from '+@StartDateLine+' to '+@EndDateLine+'</H1>' +
    N'<table border="1">' +
    N'<tr><th>EmployeeID</th>' +
        N'<th>Employee Name</th>' +
    N'<th>Date Of Work</th>' +
    N'<th>Timesheet Hours</th>' +
    N'<th>Job Numbers</th>' +
    N'<th>Daywork Numbers</th></tr>' +
 
    REPLACE(REPLACE(CAST ((SELECT td = EmployeeID,       '',
                    td = LastFirstName, '',
                    td = '<a target="_blank" href="time/printemployeetimesheet.asp?WorkDate=' + 
			 replace(convert(varchar(11),DateOfWork),' ','+') +
			 '&EmployeeName=' + replace(LastFirstName,' ','+') +
			 '">TS</a>' + convert(varchar(11),DateOfWork), '',
                    td = convert(varchar(10),Hours), '',
                    td = JobNumbers, '',
                    td = DayworkNumbers
              FROM #Entries 
                          order by DateOfWork,FirstLastName
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX)),'&lt;','<'),'&gt;','>') +
    N'</table>' +
N'  '+
N'<H1>Please verify these entries and correct any errors</H1>';

Open in new window

androman56

ASKER

Thank you. That is most helpful. The replace functions are the key and I never would have got there on my own.

There is a remaining problem driving the DateOfWork into the page to render the Timesheet.

Currently with the convert(varchar(11),DateOfWork) drives a dateformat that looks like "Mar 16 2009" whereas the page required "3/16/2009".

I tried driving it straight in without any convert but no luck.

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

androman56

ASKER

Excellent.

There were one or two other parts of the URL missing but your part was spot on. Thank you.