xp_sendmail message format

Question

xp_sendmail message format

Mark Hottinger

Mr or Mrs. 500

Points: 542
More actions
November 3, 2005 at 9:40 am

#92074

I am using xp_sendmail to send the result of a query. The outputed message received in Outlook contains 3 blank lines between every row. Is there a way for me to remove the blank lines? Here is what I am running:
EXECUTE @rc = master..xp_SendMail
                @Recipients = 'myemailaddress',
                @Query = @MsgQuery,
                @Subject = 'Test',
                @No_Output = 'FALSE',
                @No_Header = 'TRUE',
                @Width = 90,
                @DBUse = 'MyDB'
@MsgQuery is basically selecting one column from a table. The column is varchar(80). If you EXECUTE (@MsgQuery), you get all the rows together as you would expect. What am I missing? Is there an option I should be including that I am not?
Mark
Mark

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Gray SSChampion Points: 10667 More actions · Answer 1

I believe that 80 columns is wider than is supported by default with either Exchange, Outlook, or both. They will break lines apart for you because they know it is what you want. (heh)

The only workaround that I could come up with was to insert truncated data (using the LEFT() function) into a temporary table that had narrower columns, followed by SELECTing the data back from the temp table.

Hopefully someone else knows what to do and I can get rid of a rather goofy fix.

Mark Hottinger Mr or Mrs. 500 Points: 542 More actions · Answer 2

I kept experimenting with the Width parameter to the stored procedure and found that providing it a value of 320 allows me to get the eMails without the extra lines. That seems rather klugy to me but it works. I suspect that it might have something to do with converting varchar to nvarchar (possibly multiple times. I don't know. For now, I have what I wnat. I'll keep trying to find a better solution and post one if I do. If anyone else comes up with a better solution or an explanation, I would love to hear them.

Thanks,

Mark