Database Mail HTML - How to format an A HREF Link on a query coulm -- sql server reporting services

Bob Roggy
6/29/2007 11:52:01 AM

The code for the HTML e-mail is inside a stored procedure, the following is
part of the stopred procedure:

SET @hTableDetail = N'<table width=650 border="1"><font face="Times New
Roman"><Font Size="1">' +
N'<tr><th align ="left">JobNo</th>' +
N'<th align ="left">Qty</th>' +
N'<th align ="left">Product</th>' +
N'<th align ="left">Book Title</th>' +
N'<th align ="left">Tracking No</th></tr>' +
CAST ( ( SELECT td = Jobno, '',
td = ShipQty, '',
td = Product, '',
td = BookTitle, '',
td = TrackingNo, ''
FROM ShipmentsDetail
WHERE Shipment_ID = @hShipment_ID
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

SET email_body = email_body + @hTableDetail

Whenever I try to code around:

td = TrackingNo, ''

Whis is the database access for the tracking number I get the complete html
link.

Bob

[quoted text, click to view]

Bruce L-C [MVP]
6/29/2007 12:07:59 PM

What you want to do is put whatever text you want them to see (usually for
me it is a value from a field). I then underline and make it blue so they
know to click on it. Finally, right mouse click on it, properties,
navigation tab, jump to URL. If you want it to be in a separate window then
do this:
= "javascript:void(window.open('http://www.google.com'))"

The above expression could be put into the Jump to URL field.

Note you need to be on RS 2000 SP1 or greater for the above to work (i.e.
anything release from 2000 sp1 and onward).

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Bob Roggy
6/29/2007 12:26:01 PM

I am working with database mail. I have a stored procedure that executes in a
batch mode nightly that send e-mail notification for all the daily shipments.
The SQL stored procedure is sending the HTML email.

My problem is I cannot find very many examples of database HTML mail sent
from a stored procedure. One of the columns that I am retrieving from the
database is tracking number and whatever formatting that you are going to do
has to be done in the stored procedure before the email is sent.

The question is how do you format an html link on a column retrieved in a
stored procedure which is sending HTML mail?

[quoted text, click to view]

Bruce L-C [MVP]
6/29/2007 2:08:50 PM

Put it this way. If you can type the link you want into IE and have it work
you can do this from the report. Jump to URL expects something that you
would see in the address part of IE. If that is not what you are trying to
do then what you need to do is to have your stored procedure send the email.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Bruce L-C [MVP]
6/29/2007 4:13:50 PM

Ahhh, that is a different matter. If I understand you correctly you want to
click on a link and execute a stored procedure. The stored procedure will
then send the email.

I would do the following. Write the stored procedure to accept the
parameters you wish to have. Outside of RS get the stored procedure to work.
Have it return a success or fail message. Next have a report that all it
does is call the stored procedure and display the result. For instance, in
your stored procedure have something like this:

set @Returnmessage = 'Email sent'
select @Returnmessage as EmailResult
return

Create a report based on this. You can hide the report in listview so the
users don't see it. The report should have the report parameters you want to
send to the stored procedure. Then when you click on the field use the Jump
To Report (right click on field, parameters, navigation tab, jump to report)
and set the parameters appropriately.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Bob Roggy
6/30/2007 7:38:01 PM

No,

I have a VB program that rund in batch mode scheduled at 5:00PM at night.
The batch program executes a query retrieving all shipments that have been
shipped that day. For each shipment we format an HTML email. In order to do
this the program calls the following stored procedure.

EXECUTE msdb..sp_send_dbmail
@profile_name = @hProfileName,
@recipients = @hRecipients,
@copy_recipients = @hCC,
@Subject = @hEmail_Subject,
@body = @hEmail_Body,
@body_format = 'HTML',
@file_attachments = @hFile_Attachments;

Before this stored procedure is called @hEmail_Body is formated. One of the
tables that I format for this html database email is:

SET @hTableDetail = N'<table width=650 border="1"><font face="Times New
Roman"><Font Size="1">' +
N'<tr><th align ="left">JobNo</th>' +
N'<th align ="left">Qty</th>' +
N'<th align ="left">Product</th>' +
N'<th align ="left">Book Title</th>' +
N'<th align ="left">Tracking No</th></tr>' +
CAST ( ( SELECT td = Jobno, '',
td = ShipQty, '',
td = Product, '',
td = BookTitle, '',
td = TrackingNo, ''
FROM ShipmentsDetail
WHERE Shipment_ID = @hShipment_ID
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

I would like to put something around the TrackingNo what would link to
another website example FedEx or UPS where a customer can track this
shipment. So far all of my attempts at doing this shows the full <A HREF=
statemnt not just the link.

My problem is coming up with the syntax that whould either put a link on the
TrackingNo or have a link after the TrackingNo that says TrackIt. When they
would click on either the TrackingNo or Trackit we would link to the FedEx
website passing the tracking number thus shoing the tracking for the shipment.

Bob

[quoted text, click to view]

Bruce L-C [MVP]
7/1/2007 11:56:42 AM

I think you would be better off in a non reporting services newsgroup. If
you look at my responses you will see they all revolve around using reports.
You can have a field in a report with a link to click on, etc. What you are
asking has absolutely nothing to do with reporting. You would be better off
to ask a question if another group like sqlserver.programming. Your question
is 100% SQL Server Database programming question.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Database Mail HTML - How to format an A HREF Link on a query coulm