473,382 Members | 1,225 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Control characters output in attachments from xp_sendmail

I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;

EXEC master.dbo.xp_sendmail
@recipients = '<email address>',
@dbuse = 'TestDB',
@query = 'select top 50 descr from AdTable',
@message = 'nathan email test',
@subject='SQL Mail test',
@attach_results = 'true',
@width = 100,
@separator = ','

The results look like this! -

d
e
s
c
r
-
-
-
-
-

etc.

When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.

Nathan
Jul 20 '05 #1
6 2452
Hi

There is no DDL, but at a guess descr is a natural language datatype e.g
nchar or nvarchar. Try looking at convert/cast in books online to change it.

John
"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;

EXEC master.dbo.xp_sendmail
@recipients = '<email address>',
@dbuse = 'TestDB',
@query = 'select top 50 descr from AdTable',
@message = 'nathan email test',
@subject='SQL Mail test',
@attach_results = 'true',
@width = 100,
@separator = ','

The results look like this! -

d
e
s
c
r
-
-
-
-
-

etc.

When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.

Nathan

Jul 20 '05 #2
Hi John,

I don't think its to do with the data types as this strange formatting
occurs whatever the @query parameter is set to e.g.

@query = 'SELECT count(*) FROM Table1' is returned as;

-
-
-
-
-
-

5
7
8

The original data types were VARCHAR, there are no NVARCHAR columns in
the database. I'm stumped!

"John Bell" <jb************@hotmail.com> wrote in message news:<bH*********************@news-text.cableinet.net>...
Hi

There is no DDL, but at a guess descr is a natural language datatype e.g
nchar or nvarchar. Try looking at convert/cast in books online to change it.

John
"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;

EXEC master.dbo.xp_sendmail
@recipients = '<email address>',
@dbuse = 'TestDB',
@query = 'select top 50 descr from AdTable',
@message = 'nathan email test',
@subject='SQL Mail test',
@attach_results = 'true',
@width = 100,
@separator = ','

The results look like this! -

d
e
s
c
r
-
-
-
-
-

etc.

When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.

Nathan

Jul 20 '05 #3
Hi

If you save the attachment to disk as a text file does this still happen?

John

"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
Hi John,

I don't think its to do with the data types as this strange formatting
occurs whatever the @query parameter is set to e.g.

@query = 'SELECT count(*) FROM Table1' is returned as;

-
-
-
-
-
-

5
7
8

The original data types were VARCHAR, there are no NVARCHAR columns in
the database. I'm stumped!

"John Bell" <jb************@hotmail.com> wrote in message

news:<bH*********************@news-text.cableinet.net>...
Hi

There is no DDL, but at a guess descr is a natural language datatype e.g
nchar or nvarchar. Try looking at convert/cast in books online to change it.
John
"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;

EXEC master.dbo.xp_sendmail
@recipients = '<email address>',
@dbuse = 'TestDB',
@query = 'select top 50 descr from AdTable',
@message = 'nathan email test',
@subject='SQL Mail test',
@attach_results = 'true',
@width = 100,
@separator = ','

The results look like this! -

d
e
s
c
r
-
-
-
-
-

etc.

When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.

Nathan

Jul 20 '05 #4
Yes, the attachment looks exactly the same when saved to disk, and
viewed with different Editors e.g. WordPad, ConText

If I set @attach_results = 'false' to have the results returned in the
body of the e-mail, the format is fine - the problem appears to lie
with whatever writes the text file from the results, which I'm
guessing is the sqlmap70.dll?
"John Bell" <jb************@hotmail.com> wrote in message news:<mF*******************@news-text.cableinet.net>...
Hi

If you save the attachment to disk as a text file does this still happen?

John

"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
Hi John,

I don't think its to do with the data types as this strange formatting
occurs whatever the @query parameter is set to e.g.

@query = 'SELECT count(*) FROM Table1' is returned as;

-
-
-
-
-
-

5
7
8

The original data types were VARCHAR, there are no NVARCHAR columns in
the database. I'm stumped!

"John Bell" <jb************@hotmail.com> wrote in message

news:<bH*********************@news-text.cableinet.net>...
Hi

There is no DDL, but at a guess descr is a natural language datatype e.g
nchar or nvarchar. Try looking at convert/cast in books online to change it.
John
"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
> I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> have found that when I attach results as a file, every other character
> is a control character which causes each real character output on a
> separate line. I have no idea why this is happenening, I've never seen
> it before.
> The code looks like this;
>
> EXEC master.dbo.xp_sendmail
> @recipients = '<email address>',
> @dbuse = 'TestDB',
> @query = 'select top 50 descr from AdTable',
> @message = 'nathan email test',
> @subject='SQL Mail test',
> @attach_results = 'true',
> @width = 100,
> @separator = ','
>
> The results look like this! -
>
> d
> e
> s
> c
> r
> -
> -
> -
> -
> -
>
> etc.
>
> When I view the result text file with an advanced text editor I can
> see that every other character is a control - these characters are not
> in the data, I have already checked this, so it looks like its they
> are being created by SQL Server or the mail system? Any advice much
> appreciated.
>
> Nathan

Jul 20 '05 #5
Hi

I am confused by this! Please post DDL (CREATE Table, CREATE Procedure
statements), example data as insert statements that will enable someone to
replicate the problem. Also SQL Server version details may help as well as
Windows regional setting.

Thanks

John
"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
Yes, the attachment looks exactly the same when saved to disk, and
viewed with different Editors e.g. WordPad, ConText

If I set @attach_results = 'false' to have the results returned in the
body of the e-mail, the format is fine - the problem appears to lie
with whatever writes the text file from the results, which I'm
guessing is the sqlmap70.dll?
"John Bell" <jb************@hotmail.com> wrote in message

news:<mF*******************@news-text.cableinet.net>...
Hi

If you save the attachment to disk as a text file does this still happen?
John

"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
Hi John,

I don't think its to do with the data types as this strange formatting
occurs whatever the @query parameter is set to e.g.

@query = 'SELECT count(*) FROM Table1' is returned as;

-
-
-
-
-
-

5
7
8

The original data types were VARCHAR, there are no NVARCHAR columns in
the database. I'm stumped!

"John Bell" <jb************@hotmail.com> wrote in message

news:<bH*********************@news-text.cableinet.net>...
> Hi
>
> There is no DDL, but at a guess descr is a natural language datatype e.g > nchar or nvarchar. Try looking at convert/cast in books online to change
it.
>
> John
> "Nathan Griffiths" <na****@griffiths.net> wrote in message
> news:1e*************************@posting.google.co m...
> > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a)

and > > have found that when I attach results as a file, every other character > > is a control character which causes each real character output on a > > separate line. I have no idea why this is happenening, I've never seen > > it before.
> > The code looks like this;
> >
> > EXEC master.dbo.xp_sendmail
> > @recipients = '<email address>',
> > @dbuse = 'TestDB',
> > @query = 'select top 50 descr from AdTable',
> > @message = 'nathan email test',
> > @subject='SQL Mail test',
> > @attach_results = 'true',
> > @width = 100,
> > @separator = ','
> >
> > The results look like this! -
> >
> > d
> > e
> > s
> > c
> > r
> > -
> > -
> > -
> > -
> > -
> >
> > etc.
> >
> > When I view the result text file with an advanced text editor I can > > see that every other character is a control - these characters are not > > in the data, I have already checked this, so it looks like its they > > are being created by SQL Server or the mail system? Any advice much > > appreciated.
> >
> > Nathan

Jul 20 '05 #6
Hi John,

I have finally and quite by chance worked out what the problem is, and
wouldn't you know it, its caused by a bug in SQL Server 2000!

Basically, xp_sendmail returns Unicode data by default, a problem
fixed in SP1, but not updated in Books Online;

To return readable ANSI format results as a attachment I have to
include the additional xp_sendmail parameter:

@ansi_attachment = 'true'

Its on the MS support website;

http://support.microsoft.com/default...b;EN-US;280720

thanks anyway!

Nathan

"John Bell" <jb************@hotmail.com> wrote in message news:<fw*********************@news-text.cableinet.net>...
Hi

I am confused by this! Please post DDL (CREATE Table, CREATE Procedure
statements), example data as insert statements that will enable someone to
replicate the problem. Also SQL Server version details may help as well as
Windows regional setting.

Thanks

John
"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
Yes, the attachment looks exactly the same when saved to disk, and
viewed with different Editors e.g. WordPad, ConText

If I set @attach_results = 'false' to have the results returned in the
body of the e-mail, the format is fine - the problem appears to lie
with whatever writes the text file from the results, which I'm
guessing is the sqlmap70.dll?
"John Bell" <jb************@hotmail.com> wrote in message

news:<mF*******************@news-text.cableinet.net>...
Hi

If you save the attachment to disk as a text file does this still happen?
John

"Nathan Griffiths" <na****@griffiths.net> wrote in message
news:1e*************************@posting.google.co m...
> Hi John,
>
> I don't think its to do with the data types as this strange formatting
> occurs whatever the @query parameter is set to e.g.
>
> @query = 'SELECT count(*) FROM Table1' is returned as;
>
> -
> -
> -
> -
> -
> -
>
> 5
> 7
> 8
>
> The original data types were VARCHAR, there are no NVARCHAR columns in
> the database. I'm stumped!
>
>
>
> "John Bell" <jb************@hotmail.com> wrote in message news:<bH*********************@news-text.cableinet.net>... > > Hi
> >
> > There is no DDL, but at a guess descr is a natural language datatype e.g > > nchar or nvarchar. Try looking at convert/cast in books online to change
it. > >
> > John
> > "Nathan Griffiths" <na****@griffiths.net> wrote in message
> > news:1e*************************@posting.google.co m...
> > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and > > > have found that when I attach results as a file, every other character > > > is a control character which causes each real character output on a > > > separate line. I have no idea why this is happenening, I've never seen > > > it before.
> > > The code looks like this;
> > >
> > > EXEC master.dbo.xp_sendmail
> > > @recipients = '<email address>',
> > > @dbuse = 'TestDB',
> > > @query = 'select top 50 descr from AdTable',
> > > @message = 'nathan email test',
> > > @subject='SQL Mail test',
> > > @attach_results = 'true',
> > > @width = 100,
> > > @separator = ','
> > >
> > > The results look like this! -
> > >
> > > d
> > > e
> > > s
> > > c
> > > r
> > > -
> > > -
> > > -
> > > -
> > > -
> > >
> > > etc.
> > >
> > > When I view the result text file with an advanced text editor I can > > > see that every other character is a control - these characters are not > > > in the data, I have already checked this, so it looks like its they > > > are being created by SQL Server or the mail system? Any advice much > > > appreciated.
> > >
> > > Nathan

Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Greg Lindstrom | last post by:
Hello- I have a text file generated on a HP-9000 running HP-Unix with embedded control characters and would like to read it in, modify it, and write it out in PDF format. The new page character...
2
by: nowhere | last post by:
Hi, I need to preserve some ASCII control characters (CR and LF) within an XML file so I have included the data in a CDATA section. However, when parsing it using expat, I lose the CR...
8
by: Peter O'Reilly | last post by:
I have an HTML form with a textarea input box. When the user conducts a post request (e.g. clicks the submit button), an HTML preview page is presented to them with the information they have...
1
by: rcamarda | last post by:
Beyond my control: I am finding control characters (likely tab) is making its way into address fields of our operational system. This is messing me up when I load the data into our warehouse w/ BCP...
1
by: Fred Nelson | last post by:
Hi: I'm writing an error handling system for my vb.net windows application. I have an error trapping routine that is catching all unexpected errors, writing an entry in an sql database and...
3
by: Frank Niessink | last post by:
Hi list, First of all, I wish you all a happy 2006. I have a small question that googling didn't turn up an answer for. So hopefully you'll be kind enough to send me in the right direction. ...
11
by: Ron L | last post by:
I have a barcode scanner which uses a "keyboard wedge" program so that the data it scans comes through as if it was typed on a keyboard. I am trying to have the data in the barcode be displayed in...
2
by: Bazza Formez | last post by:
I have a bound field in a DetailsView control that displays free form description type data from my SQL database table (typical data is a couple of paragraphs of written product description being...
7
by: wannymahoots | last post by:
optparse seems to be escaping control characters that I pass as arguments on the command line. Is this a bug? Am I missing something? Can this be prevented, or worked around? This behaviour...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.