Blocking behavior of trigger and sp_send_dbmail

Hi Folks
I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
short: sp_send_dbmail inside of trigger results in blocked process and never
ends.
I wanted to code something simple the get notified if new rows were inserted
or updated in table.
create table t1 (col1 int, col2 int)
go
create table t2 (col1 int, col2 int)
go
create trigger tr_ins_t1 on t1 for insert, update
as
set nocount on
truncate table t2
insert t2
select * from inserted
exec msdb.dbo.sp_send_dbmail
@.recipients = 'gene_golub@.hotmail.com'
, @.subject = '!! sql1..t1 db : inserted new rows'
, @.query = 'select * from queries.dbo.t1'
go
insert t1 values(1,1)
go
side comment: inserted table was not recognized inside of query sql string
which would be executed by sp_send_dbmail so I had to use another table to
insert rows first and then to send them over.
When I insert simple row, my query hangs forewer.
What i see in sp_who: some process which is blocked by insert statement.
And this block does not resolve itself untill I go and kill process which is
bloced by insert stm.
I had no problem before using xp_sendmail inside of the triggers. Why it
become a problem with sp_send_dbmail?
Can anybody explain me what am I doing wrong?
thank you, Gene.
Hi Gene,
I have seen that behavior too. Would this work for you?
select * from queries.dbo.t1 with (nolock)
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Gene." wrote:

> Hi Folks
> I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
> short: sp_send_dbmail inside of trigger results in blocked process and never
> ends.
> I wanted to code something simple the get notified if new rows were inserted
> or updated in table.
> create table t1 (col1 int, col2 int)
> go
> create table t2 (col1 int, col2 int)
> go
> create trigger tr_ins_t1 on t1 for insert, update
> as
> set nocount on
> truncate table t2
> insert t2
> select * from inserted
> exec msdb.dbo.sp_send_dbmail
> @.recipients = 'gene_golub@.hotmail.com'
> , @.subject = '!! sql1..t1 db : inserted new rows'
> , @.query = 'select * from queries.dbo.t1'
> go
> insert t1 values(1,1)
> go
> side comment: inserted table was not recognized inside of query sql string
> which would be executed by sp_send_dbmail so I had to use another table to
> insert rows first and then to send them over.
> When I insert simple row, my query hangs forewer.
> What i see in sp_who: some process which is blocked by insert statement.
> And this block does not resolve itself untill I go and kill process which is
> bloced by insert stm.
> I had no problem before using xp_sendmail inside of the triggers. Why it
> become a problem with sp_send_dbmail?
> Can anybody explain me what am I doing wrong?
> thank you, Gene.
|||Hi Ben
Thank you for looking into this issue.
My example was simplification of real situation. Real table has about 1500
rows and wider.
nolock option does not help in that case.
Thank you, Gene.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Gene,
> I have seen that behavior too. Would this work for you?
> select * from queries.dbo.t1 with (nolock)
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Gene." wrote: