Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Carriage returns in long URL when using xp_sendmail

Posted on 2004-08-03
11
496 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
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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when creating an UPDATE Trigger in SQL 6 20
Are triggers slow? 7 11
MS SQL Server select from Sub Table 14 23
how to restore or keep sql2000  backups useful... 2 12
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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