db mail
in [Programming SQL]
|
From: Ben Nevarez on 7 Dec 2007 02:23 Hey, perhaps I am missing some information as I do not see any problem and this works for me. I created this code that works fine: create table dept (deptid int, dname varchar(30)) insert into dept values (1, 'IT') select * from dept create table emp (eid int, ename varchar(20)) create trigger trig_emp on emp after insert as exec msdb.dbo.sp_send_dbmail @profile_name = 'your_profilel', @recipients = ''your_email, @query = 'select * from yourdb.dbo.dept' , @subject = 'Inside a trigger' select * from emp insert into emp values (1, 'Jimmy Page') insert into emp values (2, 'Robert Plant') insert into emp values (3, 'John Paul Jones') A couple of questions or tests 1) Test your query alone like in select * from yourdb.dbo.dept How long is taking? Consider this time as you want to include it in a trigger 2) Test your sp_send_dbmail statement alone like in exec msdb.dbo.sp_send_dbmail @profile_name = 'your_profilel', @recipients = ''your_email, @query = 'select * from yourdb.dbo.dept' , @subject = 'Inside a trigger' Note that the example specifies database name and schema. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica "AHartman" wrote: > The query is run against another table not the trigger owning table. > > > > > > "Ben Nevarez" <BenNevarez(a)discussions.microsoft.com> wrote in message > news:2CAD9C87-FF14-4659-AE90-97B33E066B8A(a)microsoft.com... > > > > Are you running the query against the same table owning the trigger? > > > > I tried that also and I see waits of type MSQL_XP. I tried again using > > WITH > > (NOLOCK) on the query and now it works. > > > > Hope this helps, > > > > Ben Nevarez > > Senior Database Administrator > > AIG SunAmerica > > > > > > > > "AHartman" wrote: > > > >> The DBmail piece will work as long as I don't include he @query piece. > >> > >> The @query reads a table to email the content. > >> > >> Do you have an example of what you did? > >> > >> Thanks... > >> > >> > >> "Ben Nevarez" <BenNevarez(a)discussions.microsoft.com> wrote in message > >> news:91F93442-659F-45C5-B013-088129387121(a)microsoft.com... > >> > > >> > Hello, > >> > > >> > I just tested this and it worked for me. > >> > > >> > Perhaps you want to > >> > 1) Test the sp_send_dbmail alone on the query window (that is, not > >> > inside > >> > a > >> > trigger). > >> > 2) Look for errors on the Database Mail Log. > >> > > >> > Hope this helps, > >> > > >> > Ben Nevarez > >> > Senior Database Administrator > >> > AIG SunAmerica > >> > > >> > > >> > > >> > "AHartman" wrote: > >> > > >> >> Can you use sp_send_dbmail within a trigger ? > >> >> > >> >> In converting one of our old Sql7 apps to Sql2005(sp2) there is a > >> >> Table > >> >> that has a Trigger and that code called xp_sendmail which worked. > >> >> Now when converting that to Sql2005 using Dbmail it seems to hang > >> >> trying > >> >> to > >> >> Process the mail send. > >> >> > >> >> The trace showed that it was waiting msql_xpwait... > >> >> > >> >> Does anyone have code that they have used to make this work? > >> >> > >> >> > >> >> Thanks. > >> >> > >> >> > >> > >> > >
From: AHartman on 7 Dec 2007 05:37
Thanks ... I'll look at my code again. "Ben Nevarez" <BenNevarez(a)discussions.microsoft.com> wrote in message news:4ADF56E7-AB90-451E-BDA0-089EA94C26FF(a)microsoft.com... > > Hey, perhaps I am missing some information as I do not see any problem and > this works for me. I created this code that works fine: > > create table dept (deptid int, dname varchar(30)) > insert into dept values (1, 'IT') > select * from dept > > create table emp (eid int, ename varchar(20)) > create trigger trig_emp > on emp after insert > as > exec msdb.dbo.sp_send_dbmail > @profile_name = 'your_profilel', > @recipients = ''your_email, > @query = 'select * from yourdb.dbo.dept' , > @subject = 'Inside a trigger' > > select * from emp > insert into emp values (1, 'Jimmy Page') > insert into emp values (2, 'Robert Plant') > insert into emp values (3, 'John Paul Jones') > > A couple of questions or tests > 1) Test your query alone like in > select * from yourdb.dbo.dept > How long is taking? Consider this time as you want to include it in a > trigger > > 2) Test your sp_send_dbmail statement alone like in > exec msdb.dbo.sp_send_dbmail > @profile_name = 'your_profilel', > @recipients = ''your_email, > @query = 'select * from yourdb.dbo.dept' , > @subject = 'Inside a trigger' > > Note that the example specifies database name and schema. > > Hope this helps, > > Ben Nevarez > Senior Database Administrator > AIG SunAmerica > > > > "AHartman" wrote: > >> The query is run against another table not the trigger owning table. >> >> >> >> >> >> "Ben Nevarez" <BenNevarez(a)discussions.microsoft.com> wrote in message >> news:2CAD9C87-FF14-4659-AE90-97B33E066B8A(a)microsoft.com... >> > >> > Are you running the query against the same table owning the trigger? >> > >> > I tried that also and I see waits of type MSQL_XP. I tried again using >> > WITH >> > (NOLOCK) on the query and now it works. >> > >> > Hope this helps, >> > >> > Ben Nevarez >> > Senior Database Administrator >> > AIG SunAmerica >> > >> > >> > >> > "AHartman" wrote: >> > >> >> The DBmail piece will work as long as I don't include he @query piece. >> >> >> >> The @query reads a table to email the content. >> >> >> >> Do you have an example of what you did? >> >> >> >> Thanks... >> >> >> >> >> >> "Ben Nevarez" <BenNevarez(a)discussions.microsoft.com> wrote in message >> >> news:91F93442-659F-45C5-B013-088129387121(a)microsoft.com... >> >> > >> >> > Hello, >> >> > >> >> > I just tested this and it worked for me. >> >> > >> >> > Perhaps you want to >> >> > 1) Test the sp_send_dbmail alone on the query window (that is, not >> >> > inside >> >> > a >> >> > trigger). >> >> > 2) Look for errors on the Database Mail Log. >> >> > >> >> > Hope this helps, >> >> > >> >> > Ben Nevarez >> >> > Senior Database Administrator >> >> > AIG SunAmerica >> >> > >> >> > >> >> > >> >> > "AHartman" wrote: >> >> > >> >> >> Can you use sp_send_dbmail within a trigger ? >> >> >> >> >> >> In converting one of our old Sql7 apps to Sql2005(sp2) there is a >> >> >> Table >> >> >> that has a Trigger and that code called xp_sendmail which worked. >> >> >> Now when converting that to Sql2005 using Dbmail it seems to hang >> >> >> trying >> >> >> to >> >> >> Process the mail send. >> >> >> >> >> >> The trace showed that it was waiting msql_xpwait... >> >> >> >> >> >> Does anyone have code that they have used to make this work? >> >> >> >> >> >> >> >> >> Thanks. >> >> >> >> >> >> >> >> >> >> >> >> |