Solved

Carriage returns in long URL when using xp_sendmail

Posted on 2004-08-03
11
494 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 46
convert in derived column 7 27
SQL Server tables join on parse list 6 22
Attaching Database Failed ? 3 40
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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