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

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  January 20, 2009 at 5:54 am

  #929655

  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

  January 20, 2009 at 6:19 am

  #929673

  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

  January 20, 2009 at 6:36 am

  #929691

  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

  January 20, 2009 at 6:52 am

  #929704

  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.