db mail
in [Programming SQL]
|
From: AHartman on 6 Dec 2007 17:46 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: Ben Nevarez on 6 Dec 2007 19:05 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 6 Dec 2007 20:16 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: Ben Nevarez on 6 Dec 2007 21:01 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 6 Dec 2007 21:45
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. >> >> >> >> >> >> |