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

xp_SendMail.. space padding

Posted on 2002-06-10
8
289 Views
Last Modified: 2012-08-13
All,
The following SQL is run in an overnight job, the purpose to run a SQL query and email the results in a text file to a valid email address..



use Master
exec xp_sendmail @recipients = "MADMARLIN@HOME.co.uk",
                    @Message = "Please Find Enclosed The Information Pack Requests",
           @separator  = ",",
           @Query = "USE DB1 Select Forename, surname, age From TABLE1",
              @subject = "TEST ONLY",
           @attach_results = 'TRUE',
           @no_header ='True',
           @width = 1000


The process runs fine but the fields within the text file show as follows

john       ,smith      ,21  ,

Is there anyway I can stop this padding of spaces???
and so the text file would read as
john,smith,21,

The fields are all varchars with different lengths which I think may be part of the problem. The additional spaces dont sem to be stored in the DB field so I'm assuming its looking at the field max length and padding accordingly..

Any ideas??

Madmarlin

PS this question is also repeated in other topic areas but points will be given for only one answer across all areas..
0
Comment
Question by:Madmarlin
8 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 7066590
Try          

@Query = "USE DB1 Select RTRIM(LTRIM(Forename)) As Forename, RTRIM(LTRIM(surname)) As Surname, RTRIM(LTRIM(age)) As Age From TABLE1",
 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7066614
hi,

 it seems that xp_sendmail always uses fixed caracter with for sending the data. I couldn't find any solution for that except using workarounds:
* http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839
* save the output to file and send the file as attachment...

CHeers
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7066616
RTRIM doesn' work , an explicit CAST ( as VARCHAR) neither... :-(
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7066625
0
 
LVL 1

Author Comment

by:Madmarlin
ID: 7266928


Concatinating the fields works..
0
 
LVL 1

Author Comment

by:Madmarlin
ID: 7266929
Marked for deletion
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7873500
Madmarlin, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 7924959
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

860 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