sp_send_dbmail - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

sp_send_dbmail

Author

Topic

wiss.dev
Starting Member

5 Posts

Posted - 2012-07-11 : 02:50:43

hi All,
im writing a trigger an need to use sp_send_dbmail procedure.
the code below works perfect when executed in a query window, and im receiving the email
EXEC msdb..sp_send_dbmail @profile_name='TestProfile',
@recipients='me@company.com.au',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'
go

however the same code used in a trigger, show that email is queued and it is logged in sysmail_mailitems, plus all fields are exactly the same. but i don't receive the email

any ideas
thanks heaps ..

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-11 : 04:25:59

Delete and reconfigure your mail profile.

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 10:13:27

why do you need to do this from trigger? i dont think so its a good design. whats the notification that you're trying to send out? what causes email to be sent?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wiss.dev
Starting Member

5 Posts

Posted - 2012-07-11 : 21:33:02

Lionofdesert: i tried a new profile, but made no difference, i still not receiving through the trigger
viskah16: i need a trigger as an alerting system on a field change

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 09:47:12

quote:
Originally posted by wiss.dev

Lionofdesert: i tried a new profile, but made no difference, i still not receiving through the trigger
viskah16: i need a trigger as an alerting system on a field change

does that have to be synchronous in real time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wiss.dev
Starting Member

5 Posts

Posted - 2012-07-12 : 20:52:10

yes, as the status on a record changes, a certain analysis needs to be informed. can you suggest alternative options, it sounds like you are not keen on calling it from a trigger. correct?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:42:05

quote:
Originally posted by wiss.dev

yes, as the status on a record changes, a certain analysis needs to be informed. can you suggest alternative options, it sounds like you are not keen on calling it from a trigger. correct?
thanks

i would have done it by means of sql agent job. I will create a logging table which logs the change of status details along with PK column values to indicate row which got changed. the job will be invoked at frequent intervals. Each invocation checks the logging table and retrieves details of records that changed and does notification part. It finally changes record to completion state.

This will not be in real time but you can make it close to real time by choosing a suitable schedule for the job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Deepak S
Starting Member

4 Posts

Posted - 2012-07-16 : 06:33:29

It is advsiable that the database mail profile's main account used for sending mail use Windows Authentication (Database Engine Service Cridentials), and that the SMTP server recognizes the account (probably a trusted domain account). Otherwise the mails might get blocked by spam filters and firewalls.

quote:
Originally posted by wiss.dev

hi All,
im writing a trigger an need to use sp_send_dbmail procedure.
the code below works perfect when executed in a query window, and im receiving the email
EXEC msdb..sp_send_dbmail @profile_name='TestProfile',
@recipients='me@company.com.au',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'
go

however the same code used in a trigger, show that email is queued and it is logged in sysmail_mailitems, plus all fields are exactly the same. but i don't receive the email

any ideas
thanks heaps ..

Deepak S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources