sp_send_dbmail and hyperlinks with spaces

Question

Post reply

sp_send_dbmail and hyperlinks with spaces

beath

SSCrazy

Points: 2939
More actions
January 20, 2009 at 4:41 am

#127068

Hi
I'm using sp_send_dbmail to generate an email. The body of this email will contain a hyperlink to a server path that has spaces. My issue is that the hyperlink is not rendering correctly.
For example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @recipients,
@subject = 'Test',
@body = '\\server\share\'
This correctly renders the \\server\share\ as a hyperlink (turned blue and underlined)
However when I try:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @recipients,
@subject = 'Test',
@body = '\\server\share with a space in it\'
This only renders the \\server\share bit and the rest is just normal text.
I've tried enclosing the path in quotes and appending CHAR(13) to the end but I can't seem to get it working.
cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 1

replace the space with %20, so "just a name" will be "just%20a%20name"

(%20 is hex for 32, which is ASCII for space)

Wilfred
The best things in life are the simple things

beath SSCrazy Points: 2939 More actions · Answer 2

Hi Wilfred

SET @body = REPLACE('\\server\share with a space in it\', ' ', '%20')

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @profile,

@recipients = @recipients,

@subject = 'Test',

@body = @body

The path now renders correctly but the link doesn't work. I get an error

"Cannot find \\server\share%20with%20a%20space%20in%20it

Interestingly if i then copy and past this link into a new email it works?! Maybe there's something stranage about how sp_send_dbmail generates paths?

thanks

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 3

Stange, your example works for me:

1) The email is sended correctly

2) I got a (valid) link by email

3) when I open this email, I can click on the link

4) explorer opens with this UNC path

Which step is causing a problem for you?

(you're sure the UNC path exists 😉 )

Wilfred
The best things in life are the simple things

beath SSCrazy Points: 2939 More actions · Answer 4

I had to modify it slightly to:

SET @body = REPLACE('file://\\server\share with a space in it\', ' ', '%20')

This seems to work okay. Not sure why the first way worked for you and not me but it's working now.

Thanks for your help.