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.
NathanHi
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" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406011411.54e8da4f@.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|||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" <jbellnewsposts@.hotmail.com> wrote in message news:<bHXwc.2129$wx6.21830686@.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" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406011411.54e8da4f@.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|||Hi
If you save the attachment to disk as a text file does this still happen?
John
"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406072000.42435ac7@.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" <jbellnewsposts@.hotmail.com> wrote in message
news:<bHXwc.2129$wx6.21830686@.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" <nathan@.griffiths.net> wrote in message
> > news:1ee31d7.0406011411.54e8da4f@.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|||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" <jbellnewsposts@.hotmail.com> wrote in message news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> Hi
> If you save the attachment to disk as a text file does this still happen?
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406072000.42435ac7@.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" <jbellnewsposts@.hotmail.com> wrote in message
> news:<bHXwc.2129$wx6.21830686@.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" <nathan@.griffiths.net> wrote in message
> > > news:1ee31d7.0406011411.54e8da4f@.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|||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" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406131443.2191dbed@.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" <jbellnewsposts@.hotmail.com> wrote in message
news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> > Hi
> > If you save the attachment to disk as a text file does this still
happen?
> > John
> > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > news:1ee31d7.0406072000.42435ac7@.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" <jbellnewsposts@.hotmail.com> wrote in message
> > news:<bHXwc.2129$wx6.21830686@.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" <nathan@.griffiths.net> wrote in message
> > > > news:1ee31d7.0406011411.54e8da4f@.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|||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/defaul...kb;EN-US;280720
thanks anyway!
Nathan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<fwdzc.4083$Rb4.33474736@.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" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406131443.2191dbed@.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" <jbellnewsposts@.hotmail.com> wrote in message
> news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> > > Hi
> > > > If you save the attachment to disk as a text file does this still
> happen?
> > > > John
> > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > news:1ee31d7.0406072000.42435ac7@.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" <jbellnewsposts@.hotmail.com> wrote in message
> news:<bHXwc.2129$wx6.21830686@.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" <nathan@.griffiths.net> wrote in message
> > > > > news:1ee31d7.0406011411.54e8da4f@.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
