xp_sendmail "missing" messages

[birgitd]

I have a situation where I send a lot of confirmation email messages from SQL Server using xp_sendmail. I batch them and send them once a day (or once a week).

They way I do this is to run a stored procedure that pulls my recordset and cursors through it, sending a message to each row. The message goes to the individual with a copy sent to an internal email account for tracking purposes.

For some reason that I cannot figure out, I ALWAYS get the copy, but often the person that the message is addressed to does not receive their copy. The messages do not bounce back as undelivered, and this has happened enough for me to determine that it is not a fluke. When I forward my copy to the address in the message, it always arrives.

Any help in figuring out what is going on would be greatly appreciated!

Thanks in advance!!

Birgit

 

[osjohnm]

Hi

Please post your stored procedure code so that we can see how you are passing your values into xp_sendmail.

Then we can have a look

John

 

[birgitd]

Hi John, Here's the relevant portion of the stored proc:

SET @var = 'Roster for ' + @last + ' Section: ' + @section_id + ' ' + convert(char(10),getdate(),1)
SET @emailto = RTRIM(@email)

EXEC master..xp_sendmail
@recipients = @emailto,
@copy_recipients ='internal email here',
@query = 'SELECT RTRIM(LEFT(line,66)) FROM dbo.##roster order by roster_id',
@subject = @var,
@no_header = True

 

[osjohnm]

Hi

I don't think that the problem lies in the way that xp_sendmail is being used but since its the @emailto addresses that are failing how are you getting @email?

SET @emailto = RTRIM(@email)

Could you post the code that sets the value(s) for @email.
Another idea is to test that @email has a value in it while looping thru the dataset.

John