Solved

Carriage returns in long URL when using xp_sendmail

Posted on 2004-08-03
11
497 Views
Last Modified: 2011-09-20
I'm sending out an automated email using xp_sendmail. Included in the message body is a URL. I have put a break before the URL so it starts at the beginning of the line, but it still spans 2 lines.  There seems to be a carriage return or something inserted in the middle of the link after the 78th character so that when users click on it, part of the link is cut off...

eg.

http://www.somewhere.org/nonsensical/whereever.html                     --turns into

http://www.somewhere.org/nonse
nsical/whereever.html

so that only the top part is hyperlinked.

This seems to happen right after the 78th character. Is there some way to get the URL to keep together, or break across 2 lines without breaking? Changing the URL is not really an option.
0
Comment
Question by:yogic_flyer
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 9

Expert Comment

by:crescendo
ID: 11710796
From books online for xp_sendmail:

[@width =] width

Is an optional parameter setting the line width of the output text for a query. This parameter is identical to the /w parameter in the isql utility. For queries producing long output rows, use width with attach_results to send the output without line breaks in the middle of output lines. The default width is 80 characters.

OK, so it says it's for queries, but have you tried it anyway?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11710798
Have you played with the @width parameter to see if you can stop it from wrapping?  Post your xp_sendmail code...
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11712378
in thr body change the url to a href

ie.

<a href="your long url here" >short message here</a>
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 34

Expert Comment

by:arbert
ID: 11712461
You can't do that--XP_Sendmail won't send HTML--it just gets converted to plain text....
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11719483
oops, sorry for the mis information. I use smtp mail and keep fogetting about the sendmail limits..
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 11720074
Yep, I use smtp as well just because of the HTML thing--Yukon will make our lives much better with email...
0
 
LVL 1

Author Comment

by:yogic_flyer
ID: 11721144
Sorry about the late comment.
Anyway, I have tried @width and no, it doesn't seem to work on the text body. It's kind of frustrating because it makes the URLs I've included in the mail useless. Any other alternatives? I tried CDOSYS but most likely due to some administrative settings, (that I have no access to) I cannot use it - fails when it tries to create objects.
Any other ideas and/or alternatives?  Is smtp mail a viable solution? Would there be additional components to install? Generally, I think additional installations are frowned upon...
Almost forgot, the xp_sendmail code is below:

CREATE PROCEDURE dbo.SendSummaryMail

AS
            DECLARE @Administrator AS VARCHAR(10)
                        
            SELECT @Administrator = UserMail
            FROM Administrators
            WHERE AdminPosition = 'Administrator'
            
            DECLARE @ApplicationRoot AS VARCHAR(200)
            SELECT @ApplicationRoot = FieldValue
            FROM ApplicationAdminValues
            WHERE FieldName = 'RootPath'
            
            DECLARE @MailBody AS VARCHAR(500)
            DECLARE @MailTo AS VARCHAR(50)
            DECLARE @MailSubject AS VARCHAR(100)
            
            SET @MailTo =  rtrim(@Administrator)
            
            @MailSubject = 'Your Summary Information'
                        
            SET @MailBody = 'Summary informaion for date:'
                  + CONVERT(CHAR(10),GETDATE(),111) + CHAR(10) + CHAR(10)
                  + ' can be found at the following link.'
            
            SET @MailBody = @MailBody + CHAR(10) + CHAR(10)
            SET @MailBody = @MailBody + CHAR(10) + @ApplicationRoot + 'Reports/SummaryInfo.aspx?ReportDate='
                  + CONVERT(CHAR(10),GETDATE(),111)

            EXECUTE master..xp_sendmail
            @recipients = @MailTo,
            @message = @MailBody ,
            @subject =@MailSubject

GO
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 100 total points
ID: 11721243
I use BLAT to send emails. It's a command-line programme and just needs an executable to be put on the server. As such, it's easy to call within SQL Server.

http://sourceforge.net/projects/blat
0
 
LVL 9

Accepted Solution

by:
SoftEng007 earned 100 total points
ID: 11721394
if you're interested in SMTP mail check this url:

http://sqldev.net/xp/xpsmtp.htm

no exe's just a valid smtp mail server that allows forwarding.

0
 
LVL 9

Expert Comment

by:crescendo
ID: 11721632
<<no exe's just a valid smtp mail server that allows forwarding.>>

but several dll's.  ;-)
0
 
LVL 1

Author Comment

by:yogic_flyer
ID: 11728566
Hmm...I was hoping I could salvage sendmail, but looks like it's more restrictive than I thought.
Anyway, I'll look into the alternatives...points split for you 3, hopefully that sounds fair.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Trigger 8 37
How come this XML node is not read? 3 30
SQL Server Compression Decision 5 46
Trouble installing msi file with msiexe.exe 2 18
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question