Using SQL server to generate automatic emails, I’ve noticed from time to time that the emails will miscellaneously contain exclamations throughout the mail.

Here’s an example to replicate the situation. We’ll generate a table with multiple rows of the alphabet and email the contents:

declare @t table (letters varchar(100))
declare @counter int
declare @string varchar(8000)
set @counter = 0
set @string = ''

insert into @t
select 'abcdefghijklmnopqrstuvwxyz'

while (@counter < 128) begin
   insert into @t
   select letters from @t
   set @counter = @@rowcount
end

select @string = @string + letters + '<br>'
from @t

select @string

declare @mailObj int
declare @hr int

exec @hr = sp_OACreate 'CDO.Message', @mailObj out
exec @hr = sp_OASetProperty @mailObj, 'From', 'sender@server.com'
exec @hr = sp_OASetProperty @mailObj, 'HTMLBody', @string
exec @hr = sp_OASetProperty @mailObj, 'Subject', 'test'
exec @hr = sp_OASetProperty @mailObj, 'To', 'recipient@server.com'
exec @hr = sp_OAMethod @mailObj, 'Send', NULL
exec @hr = sp_OADestroy @mailObj

This should kick out the alphabet repeated 256 times, with a
tag after each alphabet. By examining the result in query analyzer, it should look just fine. However, when you go check the email that got sent, you’ll find parts of the text that look like this:

abcdefghijklmnopqrstuvwxyz
abcdefghijklmnopqrstuvwxyzabcdef! ghijklmnopqrstuvwxyz
abcdefghijklmnopqrstuvwxyz
.
.
.
abcdefghijklmnopqrstuvwxyz
abcd! efghijklm! nopqrstuvwxyz
abcdefghijklmnopqrstuvwxyz

Fortunately, the solution is fairly simple. Microsoft Outlook seems to insert these exclamation marks randomly throughout the email when the mail contains no line feeds.

So, stick a line feed after each alphabet and the exclamation marks will disappear from the email:

select @string = @string + letters + '<br>' + char(10)
from @t

Piece of cake B)