xp_sendmail formatting headache

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_sendmail formatting headache

Author

Topic

fredyjones
Starting Member

5 Posts

Posted - 2002-05-14 : 20:12:16

using sql 2000

A procedure we run pulls information out of a table and prints it. This procedure is specifically executed through @query when xp_sendmail is executing. Thus the query returns the body of the email message to be sent. The problem we are running into involves the formatting of the message body.

EXAMPLE OF BAD FORMATTING:
---------------------------
Hello,

This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a long em

ail. This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a long email. This is a l

ong email. This is a long email. This is a long email. This is a long email.
----------------------------

For what seems like no reason it appears the extended procedure is adding carriage returns to the middle of a sentence and adding a blank line. I just can't figure out why. The very same procedures work correctly using SQL 7.0.

The full message body is pulled from multiple columns. I have verified the breaks do not occurr at the end of column data. I've also changed the @width parameter to a large number to try and resolve it. I've used temp tables to store the data and that did not help.

Does anyone have any ideas why this may be happening.

dsdeming

479 Posts

Posted - 2002-05-15 : 08:02:13

Does it always print the same number of characters between carriage returns? The text column in syscomments used to be 255 characters, and when you ran sp_helptext you'd get a carriage return every 255 characters. I wonder if this is something similar?

fredyjones
Starting Member

5 Posts

Posted - 2002-05-15 : 11:21:05

quote:

Does it always print the same number of characters between carriage returns? The text column in syscomments used to be 255 characters, and when you ran sp_helptext you'd get a carriage return every 255 characters. I wonder if this is something similar?

Almost the same.....it is always around 255 chars but not exactly the same each time. One time the break will occurr at 250 chars, the next it will occurr at 260 chars but it will never occurr at the same place each time. Incidentally, I thought this would be the problem when I first noticed it but I haven't been able to pin it on this yet.

Additional info: I noticed the SQL 2000 table is keeping these columns as nvarchar data types while our SQL 7.0 server stored them as varchar. I thought in some way, maybe, the unicode transformation or implicit conversion between the two of them may be causing this problem. I haven't had much luck confirming this either. Other than the differences between SQL 7.0 and SQL 2000 that is the only change between the two servers where the xp_sendmail formats correctly and doesn't. Has anyone else had a bad experience working with nvarchar data types?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-15 : 11:33:45

Could you show me the syntax that you are using with xp_sendmail? include data types with the variables your using (if any).

fredyjones
Starting Member

5 Posts

Posted - 2002-05-15 : 12:01:38

quote:

Could you show me the syntax that you are using with xp_sendmail? include data types with the variables your using (if any).

Syntax? I apologize, but I'm not too sure what you are looking for. I'll try giving a brief view, however:

Procedure executing xp_sendmail:
--------------------------------
code....code...code

execute master.dbo.xp_sendmail @recipients = 'myself@here.com',
@subject = 'Outgoing email',
@query = 'execute (db)..FormatEmail',
@width = 1530,
@no_header = True,
@separator = ''

end

Procedure formatting emails body called from @query
(db)..FormatEmail
--------------------------------------
code..code..code
DECLARE @vchPart1 VARCHAR(2000),
@vchPart2 VARCHAR(2000),
@vchPart3 VARCHAR(2000),
@vchPart4 VARCHAR(2000),
@vchPart5 VARCHAR(2000)

declare email_cursor cursor for

select rtrim(isnull(nvchEmailPart1, '')), /**nvchEmailPart1 column stored as NVARCHAR**/
rtrim(isnull(nvchEmailPart2, '')), /**nvchEmailPart2 column stored as NVARCHAR**/
rtrim(isnull(nvchEmailPart3, '')), /**nvchEmailPart3 column stored as NVARCHAR**/
rtrim(isnull(nvchEmailPart4, '')), /**nvchEmailPart4 column stored as NVARCHAR**/
rtrim(isnull(nvchEmailPart5, '')) /**nvchEmailPart5 column stored as NVARCHAR**/

from EmailResponse
where iEmailId = ##### /**Number representing correct email ID**/

open email_cursor

fetch next from email_cursor

into @vchPart1, @vchPart2, @vchPart3, @vchPart4, @vchPart5

while @@fetch_status = 0

begin

print @vchPart1 + @vchPart2 + @vchPart3 + @vchPart4 + @vchPart5

end
close email_cursor
deallocate email_cursor

code..code..code
-----------------------------

Some other things happen in the email_cursor that require a cursor, but they don't affect the final output.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-15 : 12:19:12

I think it has something to do with how your doing your @query...
What is the output if you just run

execute (db)..FormatEmail

?

another comment... the @query part...
The @query I find best used if your putting something like select * from table ... into your email. I prefer the use of @msg
if your building something like 'this is a long email (repeat)'.

fredyjones
Starting Member

5 Posts

Posted - 2002-05-15 : 12:48:25

quote:

I think it has something to do with how your doing your @query...
What is the output if you just run

execute (db)..FormatEmail

If I execute the FormatEmail proc the output is formatted correctly within something like query analyzer. Which throws me for a loop. My pain surrounds the fact that SQL 7.0 outputs the data without throwing in its' own breaks and carriage returns.

**DISCLAIMER** I am not the original author of these procedures, although I feel I soon will be.

I have retested the proc outputting the email body through variables and sending an email through @message but this is inheriently limiting. Just to capture the random length of any email I need to pass up to 20 vars back to the original proc which is much more intensive than it is now. Also, this requires me to almost rewrite the original FormatEmail procedure which I'm really not looking forward to doing just because SQL 2000 flubs the output. (Reinventing the wheel ya know.)

Plus, I've never run into any documentation on the limitations of the @query parameter. Most docs I've read only assert the output of a query or proc will be passed back to xp_sendmail and delivered in the body of the email (unless attachments are enabled.) Do you have any info on the limitations of @query within xp_sendmail?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-15 : 13:03:46

No more then what you seem to have there.

I'm thinking you problem is because of the print statement though.

print @vchPart1 + @vchPart2 + @vchPart3 + @vchPart4 + @vchPart5

to be exact. is it possible to set a variable like @querytext

@querytext = 'execute (db)..FormatEmail'

and then set in the xp

@query = @querytext

?

I've never experienced this problem when I moved to sql 2000 and I think it's because of the way the 'execute (db)..FormatEmail' is being handled.

Just an idea but it might be the right way to go. I wish I had a better idea of what the output of 'execute (db)..FormatEmail' is and what exactly its setting @query in the xp_sendmail to be.

fredyjones
Starting Member

5 Posts

Posted - 2002-05-15 : 13:14:20

quote:

is it possible to set a variable like @querytext

@querytext = 'execute (db)..FormatEmail'

and then set in the xp

@query = @querytext

?

Tried this also without luck. It still outputs the text with the same breaks.

quote:

I've never experienced this problem when I moved to sql 2000 and I think it's because of the way the 'execute (db)..FormatEmail' is being handled.

I wish that were the case for me.

Not sure what you mean by the way the proc is being handled. Do you mean by SQL or within itself?

quote:

Just an idea but it might be the right way to go. I wish I had a better idea of what the output of 'execute (db)..FormatEmail' is and what exactly its setting @query in the xp_sendmail to be.

Let me know what else you would like to see and I should be able to put it out there. The code is nearly identical (besides the snips) without the actual data from the tables.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-15 : 13:34:19

Well, here.. maybe this will help... It was the code I used.. or the idea behind it.

Created a table call emails_to_be_sent
with 4 columns. query, message, destination, and subject.

Every time an email was to be sent, this table would be populated. In your case, run an insert statement to insert values. In my case a stored procedure was run to help format the data (somewhat like that format emal procedure of yours) and the sp would do the insert itself.

query = (execute format email)
message = well.. nothing if you don't need
destination = 'myemail@home.com'
subject = 'my subject'
(ya I know the syntax isn't right.. going for the idea here)

On this table there'd be a trigger on insert that would take these values from inserted and give the to a stored procedure as variables.

The stored proc would then take @query, @message, @destination, @subject and run something like this (the reason the stored proc is needed like that is becase triggers can't execute anything outside of the database their created in and xp_sendmail is in master)

execute master.dbo.xp_sendmail @recipients = @destination,
@subject = @subject,
@query = @query,
@message = @message
@no_header = True,
@separator = ''

This was first created in 7.0 and just moved to 2000 with no problems often sending email with paragraphs with 3000 characters and the formatting worked fine.
Although, I don't believe we set the width, don't see why that would be different.

I thought I'd post that so you know the info I'm trying to draw on to see why yours is doing what it is.

could you post up the data that (db)..FormatEmail is putting into @query?

Edited by - M.E. on 05/15/2002 13:35:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources