Ahoi,
Shortversion :
Executing TSQL Script to send Mail -->Mail is sent --> includes additional receiver (old corpse mail)
Longer Version:
i have a weird problem that i can't seem to find the origin of.
I use the later mentioned TSQL Script to run send mails to users and me.
Which users are notified depends on the mails manually mainted in a table.
The mail receivers depend on a manuelly mainted list in a table.
But for some reason when i execture the script it adds an additional mail to the list of receivers, which is not in the list which is obtained by the following type of query:
--Returns: Mail1@xx.com; Mail2@xx.com;...
select mails from dbo.mails where job = 'X'
This is the jist of the script which is executed to send the mail.
declare @receiver nvarchar(max)
set @receiver = (select mails from dbo.mails where job = 'X')
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Name',
@recipients = @receiver,
@body= 'Mailbody' ,
@subject = 'Mailsubject',
@importance = 'HIGH';
But when ever this script is executed and the receivers are defined by the dbo.mails table it always adds one more mail ontop which i want to know how to change or get rid off if possible. Changing the entries in the dbo.mails also changes the mail receivers, except for the constant corpse mail which is always there.
Is there some sort of adminstrator mail setting?
I want to be the very best
Like no one ever was
March 3, 2022 at 2:20 pm
dbo.mails is your data table, not a SQL Server Database Mail table.
If you don't want all the recipients returned by your query, then you need to either edit the data or find a way to filter it. But be cautious -- why does it have multiple email addresses if the intent isn't to use those multiple emails?
If, for some reason, you can just arbitrarily use the first email address in dbo.mails.mails, then it would be easy to parse that -- e.g,
declare @receiver nvarchar(max) = 'Mail1@xx.com; Mail2@xx.com;'
SET @receiver = LEFT(@receiver,CHARINDEX(';',@receiver)-1);
SELECT @receiver;
March 3, 2022 at 2:57 pm
When you run this query
select mails from dbo.mails where job = 'X'
Is the value of "mails" the proper list of recipients? Does it contain the corpse email? If so, modify the data in the table.
If the values contained in the "mails" column are correct, and do not contain the corpse email, then the must be some code that was created to do this. It's not any kind of setting in SQL Server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 3, 2022 at 7:32 pm
I would check the system stored procedure, i.e. msdb.dbo.sp_send_dbmail, to see if the sp has been modified to append a fixed mail address to any list of email addresses passed to the sp. This is pretty irregular, but the sp can be modified.
Ok i found the solution, apparently there is a setting/column "REPLY TO" in the sys tables.
Thanks to a query i found on stackoverflow, in the results of a sys table i found the mail beeing placed in a sys table probably as some sort of configuration during the setup of the mail account.
I ran the following update to remove the mail account.
As a result, if someone replies to the notification mails, the reply goes back to the sys mail account insntead of the the mail account which was deposited there before the update. Actually kinda useful feature so people don not reply to the sys mail, but seems like a pain to find if you do not know where to search.
--check settings
select *
from msdb.dbo.sysmail_profile p
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
join msdb.dbo.sysmail_server s on a.account_id = s.account_id
--update the relevant column by getting rid of the mail
update a
set a.replyto_address = ''
from msdb.dbo.sysmail_profile p
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
join msdb.dbo.sysmail_server s on a.account_id = s.account_id
I want to be the very best
Like no one ever was
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply