By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Control characters output in attachments from xp_sendmail

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.