Solved

Carriage returns in long URL when using xp_sendmail

Posted on 2004-08-03
11
491 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now