| Author |
Topic |
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
 Posted - 2011-03-22 : 03:57:29
|
| I'd like to call dll from trigger to send out email in SQL 2005.Should I use sp_addextendedproc to register,then what is the next step ?Appreciate if someone gives me some example. Thanks. |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-03-22 : 04:10:09
|
| see if this helps :http://www.datasprings.com/resources/articles-information/creating-email-triggers-in-sql-server-2005 |
 |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-22 : 09:02:14
|
| Really, why not just use Database Mail instead of installing something?If you must use your custom DLL, then you need to make it Extended Stored Procedure. This feature is deprecated however, and CLR routine is the way to write custom code for SQL Server today. |
|
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2011-03-22 : 09:52:02
|
Ok. I tried database mail and that worked for first 2 attemps.But I got error when I tried againThe mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2011-03-22T22:44:23). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.0.0 Tarpitting active for [xxx.xxx.xxx.xxx]).)Thanksquote:
Originally posted by russell
Really, why not just use Database Mail instead of installing something?If you must use your custom DLL, then you need to make it Extended Stored Procedure. This feature is deprecated however, and CLR routine is the way to write custom code for SQL Server today.
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-22 : 11:30:16
|
| Probably want to disable tarpitting from internal clients. Talk to your network/email admin.Did you flood the server with emails? ;)By the way, emailing from a trigger is not generally considered a good idea. Better to log something to a table in the trigger and have a job query the table periodically and send out emails. |
|
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2011-03-24 : 04:04:26
|
That worked fine after enable database mail in sql Agent.But getting another error when I tried on other server (different station). As per error message, I suspect that SMTP port is blocked. Do you think so ?Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)quote:
Originally posted by russell
Probably want to disable tarpitting from internal clients. Talk to your network/email admin.Did you flood the server with emails? ;)By the way, emailing from a trigger is not generally considered a good idea. Better to log something to a table in the trigger and have a job query the table periodically and send out emails.
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-24 : 09:53:38
|
| Need to enable relay for this server in the SMTP server properties. |
|
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2011-03-25 : 01:55:14
|
Problem solved to send email.Another problem is it doesnt work when I insert record from interface but it works fine when I run the trigger code in query window.Any idea ?thanks.quote:
Originally posted by russell
Need to enable relay for this server in the SMTP server properties.
|
|
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2011-03-25 : 03:56:59
|
Problem solved.Exec msdb.dbo.sp_addrolemember 'DatabaseMailUserRole','NT Authority\network service'--------------------------------------------------------------------------------quote:
Originally posted by kwikwisi
Problem solved to send email.Another problem is it doesnt work when I insert record from interface but it works fine when I run the trigger code in query window.Any idea ?thanks.quote:
Originally posted by russell
Need to enable relay for this server in the SMTP server properties.
|
|
|
|
|
How to call Dll From Trigger ???