| Author |
 Topic  |
|
|
wiss.dev
Starting Member
Australia
5 Posts |
 Posted - 07/11/2012 : 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
Pakistan
864 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/11/2012 : 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
Australia
5 Posts |
Posted - 07/11/2012 : 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
India
47040 Posts |
Posted - 07/12/2012 : 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
Australia
5 Posts |
Posted - 07/12/2012 : 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
India
47040 Posts |
Posted - 07/13/2012 : 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
India
4 Posts |
Posted - 07/16/2012 : 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 |
|
|
| |
Topic |
|
sp_send_dbmail
