Blocking Problem

Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked serve
r
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:

> Hi there,
> maby someone know something about this kind of strange behaviour. One of m
y
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the sa
me
> SPID. The locks are on key-level. I've tested it and the problem seems to
be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fin
e
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...[vbcol=seagreen]
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f 234 spam-mails.
Betalende brugere fr ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after prope
r
evaluation and testing!)
John
"Bobby Henningsen" wrote:

> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Serve
r
> 2000 (And still are. They had to move the database back). I can'tfigure ou
t
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thin
g
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f? 234 spam-mails.
> Betalende brugere f?r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>

Blocking Problem

Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked server
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of my
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the same
> SPID. The locks are on key-level. I've tested it and the problem seems to be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fine
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards :)
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>> Hi there,
>> maby someone know something about this kind of strange behaviour. One of
>> my
>> colleagues has made an ASP appl. using SQL 2000 and I helped him move
>> data
>> to another SQL Server 2000 . We just detached and attached and he changed
>> the connectionstring and it just worked fine... For a while. Suddenly the
>> users reported that the couldn't get a certain list of records and when I
>> checked the server there was a couple of X-locks on a table held by the
>> same
>> SPID. The locks are on key-level. I've tested it and the problem seems to
>> be
>> the Transaction handling in some way. I started the profiler and filtered
>> the trace to see
>> the SPID who made the locks and the T-SQL looked something like this :
>> Begin Transaction
>> Select...
>> Update...
>> Select..
>> Another one
>> Begin Transaction
>> Insert...
>> No commit or rollback even though my colleague tells me that he either
>> commits or rolls back his transaction in his code. But it looks like it
>> never hits the server ' Is this a known issue ?
>> Could it be a Service Pack issue. There's no SP's installed. It worked
>> fine
>> when we were running on the old server.. One difference between the old
>> server and the new server is that the new one is installed as a named
>> instance. A lot of the select statements uses a linked server but not the
>> actual update, insert, and delete statement. they're on the loval server.
>> All the locks has Owner type XAct. I really cannot figure out why the
>> Transactions hang. Is there any known issues about named instances and
>> linked servers ?
>> Anybody got a clue ?
>> Regards .)
>> Bobby Henningsen
>>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 234 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after proper
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Server
> 2000 (And still are. They had to move the database back). I can'tfigure out
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thing
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards :)
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> > Hi Bobby
> >
> > Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> > say which events you are profiling, but have you included transactions/SQL
> > Transactions? IT is not unknown for profiler to not include some logging,
> > expecially on a busy server and you are profiling on the same server.
> >
> > You don't say if you have performed any maintenance on this database, you
> > may want to defragment the indexes and update the statistics.
> >
> > Without seeing the actual code it is hard to comment on if it can be
> > improved. You may want to see if you need the select statement before the
> > update, or if you are unneccesarily wrapping select statements in
> > transactions. If your select statement before the update uses a linked
> > server
> > this may force the transaction to be a distributed transaction.
> >
> > John
> >
> > "Bobby Henningsen" wrote:
> >
> >> Hi there,
> >> maby someone know something about this kind of strange behaviour. One of
> >> my
> >> colleagues has made an ASP appl. using SQL 2000 and I helped him move
> >> data
> >> to another SQL Server 2000 . We just detached and attached and he changed
> >> the connectionstring and it just worked fine... For a while. Suddenly the
> >> users reported that the couldn't get a certain list of records and when I
> >> checked the server there was a couple of X-locks on a table held by the
> >> same
> >> SPID. The locks are on key-level. I've tested it and the problem seems to
> >> be
> >> the Transaction handling in some way. I started the profiler and filtered
> >> the trace to see
> >> the SPID who made the locks and the T-SQL looked something like this :
> >>
> >> Begin Transaction
> >> Select...
> >> Update...
> >> Select..
> >>
> >> Another one
> >>
> >> Begin Transaction
> >> Insert...
> >>
> >> No commit or rollback even though my colleague tells me that he either
> >> commits or rolls back his transaction in his code. But it looks like it
> >> never hits the server ' Is this a known issue ?
> >> Could it be a Service Pack issue. There's no SP's installed. It worked
> >> fine
> >> when we were running on the old server.. One difference between the old
> >> server and the new server is that the new one is installed as a named
> >> instance. A lot of the select statements uses a linked server but not the
> >> actual update, insert, and delete statement. they're on the loval server.
> >> All the locks has Owner type XAct. I really cannot figure out why the
> >> Transactions hang. Is there any known issues about named instances and
> >> linked servers ?
> >>
> >> Anybody got a clue ?
> >>
> >> Regards .)
> >> Bobby Henningsen
> >>
> >>
> >>
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 234 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>

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:

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 nev
er
> ends.
> I wanted to code something simple the get notified if new rows were insert
ed
> 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:
>

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:
> 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.

Black area where column is hidden in a table when exporting to PDF

I have a very strange problem. I'm using a table and I've
conditionally hidden columns before without any problems, but all of a
sudden, this one report I'm working on has a conditionally hidden
column in the table, but when the column is hidden and I export the
report as PDF, there is a solid black rectangle. It's not directly
where the column is hidden (the hidden column is the next to last
column to the right), but it's one column further to the
right...outside the table. So for instance, if all the columns were
shown in the table, the black rectangle would be to the right of the
table...where another column would go if I were to add one more on the
end. So with the hidden column, it basically goes...end of the table,
white space column, then black rectangle.
Anyone have any ideas what's up with this? I'm using reporting
services 2000 with SP2. Thanks.
RyanHi Ryan
[apologies if you have already received a mail through google groups from me]
did you ever find a solution to this issue, i am having exactly the same
problem of a black square being tacked on to the end of a table?
I'd be grateful if you do have a solution
thanks
"Ryan" wrote:
> I have a very strange problem. I'm using a table and I've
> conditionally hidden columns before without any problems, but all of a
> sudden, this one report I'm working on has a conditionally hidden
> column in the table, but when the column is hidden and I export the
> report as PDF, there is a solid black rectangle. It's not directly
> where the column is hidden (the hidden column is the next to last
> column to the right), but it's one column further to the
> right...outside the table. So for instance, if all the columns were
> shown in the table, the black rectangle would be to the right of the
> table...where another column would go if I were to add one more on the
> end. So with the hidden column, it basically goes...end of the table,
> white space column, then black rectangle.
> Anyone have any ideas what's up with this? I'm using reporting
> services 2000 with SP2. Thanks.
> Ryan
>