Deadlocks

Any ideas on resolving deadlocks on a sql-server 2000 windows 2000
platform?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
http://support.microsoft.com/?id=224453
118552 INFO: Handling Deadlock Conditions
http://support.microsoft.com/?id=118552
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

deadlock with Win2003 and Clustered SQL Server 2000

Hi!
We've encountered a strange deadlock problem when migrating to a loadbalance
d Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on
two Windows 2000 servers.
The scenario is that we receive 10.000 records in XML which need to be inser
ted into - or updated in the database depending on whether we already know t
he record or not. The problem is that the system suddenly deadlocks while ru
nning a simpel UPDATE. The
UPDATE does however results in triggering an update trigger which in turn tr
iggers another trigger. This has not been a problem on 3 other production se
rvers running the same software and receiving the same amount of data. The o
nly difference is the use o
f Win2003 as frontend and a clustered SQL Server.
We do not currently use Explicit Transactions - but has tried it without any
luck. We've done traces which show the records involved in the deadlock has
n't got any obvious relations - other than being in the same table (differen
t primary keys al around).
A friend of mine has experienced a similary problem - and said it had someth
ing to do with Win2003 and SQL2000 together, but can't find any information
regarding this particullar problem...
Any hint would be greatly appriciated
Best regards,
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs s
ome fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND d
eletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Lef
t(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupi
d;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap
a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two diff
erent records that you are sure of doesn't link to any shared records throug
h foreign keys!
Regards,
Michael B. Hansen
-- Michael B. Hansen wrote: --
Update(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...

quote:

---

> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two

---

different records that you are sure of doesn't link to any shared records
through foreign keys!

quote:

---

>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -

---

and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.

quote:

---

> The way to reproduce the problem is to have an UPDATE-trigger that

---

SELECTs some fields from the updated row and UPDATEs another row:

quote:

---

> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE

---

personid=797 AND deletetime IS NULL AND userpoolid=0

quote:

---

>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,

---

50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;

quote:

---

> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections

---

and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:

quote:

---

> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen

---

sql

deadlock with Win2003 and Clustered SQL Server 2000

Hi
We've encountered a strange deadlock problem when migrating to a loadbalanced Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on two Windows 2000 servers
The scenario is that we receive 10.000 records in XML which need to be inserted into - or updated in the database depending on whether we already know the record or not. The problem is that the system suddenly deadlocks while running a simpel UPDATE. The UPDATE does however results in triggering an update trigger which in turn triggers another trigger. This has not been a problem on 3 other production servers running the same software and receiving the same amount of data. The only difference is the use of Win2003 as frontend and a clustered SQL Server
We do not currently use Explicit Transactions - but has tried it without any luck. We've done traces which show the records involved in the deadlock hasn't got any obvious relations - other than being in the same table (different primary keys al around)
A friend of mine has experienced a similary problem - and said it had something to do with Win2003 and SQL2000 together, but can't find any information regarding this particullar problem...
Any hint would be greatly appriciated :
Best regards
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two different records that you are sure of doesn't link to any shared records through foreign keys
Regards
Michael B. Hanse
-- Michael B. Hansen wrote: --
Update(!
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row
initial UPDATE
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=
trigger
(...) start of trigger (...
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email
BEGI
SELECT fname, lname, groupid, email INTO #tmp FROM inserte
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid
END
(...) end of trigger (...
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE
while 1=1 begi
UPDATE gc_persons ...
en
Do anyone know of a fix for this
Regards
Michael B. Hansen|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...
> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two
different records that you are sure of doesn't link to any shared records
through foreign keys!
>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -
and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.
> The way to reproduce the problem is to have an UPDATE-trigger that
SELECTs some fields from the updated row and UPDATEs another row:
> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE
personid=797 AND deletetime IS NULL AND userpoolid=0
>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,
50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;
> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections
and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen|||I've found a workaround for the problem
It seems that SP3 has some kind of a bug with regards to update-triggers
1) The 'UPDATE(field)' function doesn't seem to give do the check correctly - it seems to always return true
eg.
IF UPDATE(field1
BEGI
END
is always execute
2) The "UPDATE <table> SET field=value FROM inserted" seems to lock a whole page (or something like that) instead of the individual rows it updates. The workaround to this is to include WITH (UPDLOCK) in the UPDATE-clause
eg.
UPDATE <table> WITH (UPDLOCK) SET field=value FROM inserte
These two issues first showed themselves after(!) we updated to SP3 - and we've only been able to reproduce the second issue on a clustered SQLServer2000 running on a Windows Server 2003
Regards
Michael B. HAnsen

Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GO
Hi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>
|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe

> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statements
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>
|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a crit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe

> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a crit :
>
|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:

> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.

Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GOHi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe
> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statements
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a écrit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe
> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a écrit :
>> Hello,
>> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
>> have a windows app that polls a web-service every few seconds, and the
>> web service logs that communication in a table. Since there's no
>> reason to keep really old log entries, and in order to keep the table
>> size down, I wrote a trigger to delete all records older than x days
>> (x=5 in this case).
>> Shortly after adding the trigger, I started getting frequent deadlock
>> errors. The table (when pruned by the trigger) is about 100k records
>> or so. Also, there is more than one copy of the windows app polling
>> the web-service, so two polling events could occur at the same time.
>> I've included the source for both the stored proc and the trigger
>> below. At first I thought the problem had to do with the "select" stmt
>> at the end of the stored proc, but adding the (NOLOCK) hint did not
>> solve the problem.
>> Any idea what might be causing the deadlock?
>> TIA,
>> Gabe
>>
>> -- 8< --
>> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
>> @.StoreKey uniqueidentifier,
>> @.dateAdded datetime,
>> @.Source nvarchar(100)
>> AS
>> INSERT INTO dbo.[StoreCommunicationLog](
>> [StoreKey],
>> [dateAdded],
>> [Source]
>> ) VALUES (
>> @.StoreKey,
>> @.dateAdded,
>> @.Source
>> )
>> SELECT
>> [StoreCommunicationLogID],
>> [StoreKey],
>> [dateAdded],
>> [Source]
>> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
>> WHERE
>> [StoreCommunicationLogID] = @.@.IDENTITY
>> GO
>> -- 8< --
>> CREATE TRIGGER trig_StoreCommunicationLog
>> ON StoreCommunicationLog
>> FOR INSERT
>> AS
>> DECLARE @.StoreKey UNIQUEIDENTIFIER
>> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
>> DECLARE simpleCursor CURSOR
>> LOCAL
>> KEYSET
>> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
>> WHERE (StoreKey = @.StoreKey) AND
>> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
>> DECLARE @.id int
>> OPEN simpleCursor
>> FETCH LAST FROM simpleCursor
>> INTO @.id
>> CLOSE simpleCursor
>> DEALLOCATE simpleCursor
>> DELETE FROM StoreCommunicationLog
>> WHERE (StoreKey = @.StoreKey)
>> AND (StoreCommunicationLogID < @.id)
>> GO
>|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.sql

Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GOHi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe

> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statement
s
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a crit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe

> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a crit :
>|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:

> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.

dead lock problem

Version: SQL Server 2000 8.00.818
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have been asked to look into a problem in one of the database
at our client site. I have very little idea of the application.
It seems they are facing intermittent deadlock problem. This is the
query of the session which is *always* rolled back.
SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
...
FROM air_itin_price, air_itin_fare_calc
WHERE air_itin_price.air_itin_id = ?
AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_id
order by air_itin_fare_calc.air_itin_price_id
The index on the two tables
ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
(
[air_itin_id],
[psgr_type]
) WITH FILLFACTOR = 50 ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
[dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMARY]
CREATE INDEX [air_itin_price_airitinpriceid] ON
[dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
This is a read only query only, even though the isolation level is same for
all sessions (SERIALIZABLE).
Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer wi
ll
use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behavio
r
same with CLUSTERED INDEX also. I also notice that the primary key on the ta
ble
air_itin_price is a composite index on air_itin_id + psgr_type. But the quer
y
is only for air_itin_id. Does that make a difference?
Any pointers will be appreciated."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_i
d
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same fo
r
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behav
ior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the qu
ery
> is only for air_itin_id. Does that make a difference?
> Any pointers will be appreciated.
some more info from trace:=
Deadlock encountered ... Printing deadlock information
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Wait-for graph
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:1
2004-03-19 14:12:34.65 spid4 PAG: 6:1:3120 CleanCnt:1 M
ode: S Flags: 0x2
2004-03-19 14:12:34.65 spid4 Grant List 0::
2004-03-19 14:12:34.65 spid4 Owner:0x42bcba80 Mode: S Flg:0x0
Ref:1 Life:00000000
SPID:178 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 178 ECID: 0 Statement Type: EXECUT
E Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: RPC Event: sp_cursorfetch;1
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: IX SP
ID:76 ECID:0
Ec0x713CF510) Value:0x42bd18e0 Cost0/3F0)
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:2
2004-03-19 14:12:34.65 spid4 PAG: 6:1:10267 CleanCnt:1 M
ode: IX Flags: 0x0
2004-03-19 14:12:34.65 spid4 Grant List 2::
2004-03-19 14:12:34.65 spid4 Owner:0x42bd3080 Mode: IX Flg:0x0
Ref:0 Life:02000000
SPID:76 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 76 ECID: 0 Statement Type: INSERT
Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: Language Event: INSERT INTO a
ir_itin_price
(air_itin_id,psgr_type,quantity,pub_fare
,base_fare,q_charge,other_charges,tt
l_markup,ttl_tax,securit
y_fee,fare_tax_rate, us1_tax) VALUES
(398529,0,1,237.24,183.10,0.00,0.00,0.00,44.14,10.00,0.0000,13.74)
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPI
D:178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
2004-03-19 14:12:34.65 spid4 Victim Resource Owner:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:
178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
Looks like it is a conversion deadlock.|||both sessions are using READ COMMITTED.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id =
air_itin_fare_calc.air_itin_price_id
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same
for
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the
behavior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the
query
> is only for air_itin_id. Does that make a difference?
>
Perhaps. If the query used the key, then it's locks would be more narrow.
It looks like the row being inserted by one client might belong in the
resultset of for the other client. If the query specified the full key, it
might be clear to SQL that that is not the case.
Also the query is using
sp_cursorfetch;1
What kind of cursor is the client using?
David

DDL Permissions - CREATE PROCEDURE, but no CREATE TABLE

Env: SQL Server 2000 SP3a on Windows 2k Server SP4
I want to allow devlopers to create and alter sprocs in the dbo
schema, but not create or alter tables (in any schema). I tried:
GRANT CREATE PROCEDURE TO <user>
...but it will only allow the user to create procedures within their
own user schema - not in the dbo schema. "Server: Msg 2760, Level 16,
State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
does not exist or you do not have permission to use it."
So then I tried adding the user to the ddl_admin fixed db role and
then executing:
DENY CREATE TABLE TO <user>
...I thought I was OK at first. The user could create dbo owned
sprocs, alter them, and not create tables in any schema. BUT, they
can DROP TABLE! Of course, you can't DENY DROP <object>.
Any idears?
TIA,
-PeterHave you tried to use only the REFERENCES permission on the table for the
user creationg the SP? Check the "Owners and Permissions" topic in Books
OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar
chitec.chm::/8_ar_da_2s4z.htm).
HTH,
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0401021018.12de3b15@.posting.google.com...

quote:

---

> Env: SQL Server 2000 SP3a on Windows 2k Server SP4
> I want to allow devlopers to create and alter sprocs in the dbo
> schema, but not create or alter tables (in any schema). I tried:
> GRANT CREATE PROCEDURE TO <user>
> ...but it will only allow the user to create procedures within their
> own user schema - not in the dbo schema. "Server: Msg 2760, Level 16,
> State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
> does not exist or you do not have permission to use it."
> So then I tried adding the user to the ddl_admin fixed db role and
> then executing:
> DENY CREATE TABLE TO <user>
> ...I thought I was OK at first. The user could create dbo owned
> sprocs, alter them, and not create tables in any schema. BUT, they
> can DROP TABLE! Of course, you can't DENY DROP <object>.
> Any idears?
> TIA,
> -Peter

---

|||Thank you for the reposnse, but I think you missed the question. Your
reponse is angled towards data access permissions. My question is
about object creation permissions. I want a devloper to be able to
CREATE and ALTER stored procedures in the dbo schema, but not be able
to CREATE, ALTER, or DROP tables or other objects.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<ua9a#ud0D
HA.2324@.TK2MSFTNGP09.phx.gbl>...[QUOTE]
> Have you tried to use only the REFERENCES permission on the table for the
> user creationg the SP? Check the "Owners and Permissions" topic in Books
> OnLine
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
ar
> chitec.chm::/8_ar_da_2s4z.htm).
> HTH,
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0401021018.12de3b15@.posting.google.com...|||Sorry, you are correct, I misread the original message. Unfortunately I
don't think it is possible to acheive what you want to achieve. I guess you
should take care who can create the procedures, so you can trust the person,
if you want this person to use the dbo user.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0401051432.6deec0fb@.posting.google.com...

quote:

---

> Thank you for the reposnse, but I think you missed the question. Your
> reponse is angled towards data access permissions. My question is
> about object creation permissions. I want a devloper to be able to
> CREATE and ALTER stored procedures in the dbo schema, but not be able
> to CREATE, ALTER, or DROP tables or other objects.
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in

---

message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...[QUOTE]
the[QUOTE]
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar[QUO
TE]|||I cam to the same conclusion thru my research. Will SQL Server Yukon
provide better permissions granularity to provide what I'm looking
for?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<#pcwG6C1D
HA.2224@.TK2MSFTNGP10.phx.gbl>...[QUOTE]
> Sorry, you are correct, I misread the original message. Unfortunately I
> don't think it is possible to acheive what you want to achieve. I guess yo
u
> should take care who can create the procedures, so you can trust the perso
n,
> if you want this person to use the dbo user.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0401051432.6deec0fb@.posting.google.com...
> message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...
> the
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books
\ar|||Does anyone have any other solutions to this? It seems like it should
be very easy to allow developers to create and alter sprocs under the
dbo schema, but not do any other DDL (such as DROP TABLE or DROP
PROCEDURE).
nospampedro@.yahoo.com (Peter Daniels) wrote in message news:<2fd8f155.0401081203.4e75bc46@.posting.
google.com>...[QUOTE]
> I cam to the same conclusion thru my research. Will SQL Server Yukon
> provide better permissions granularity to provide what I'm looking
> for?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:<#pcwG6C1DHA.2224@.TK2MSFTNGP10.phx.gbl>...
> message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...
> the
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books
\ar

DCOM error

I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:

The request failed with HTTP status 400: Bad Request.

One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.

The new ReportServer VD appears to work (although no reports are installed to test).

Thanks for any help getting this issue resolved!

I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

Anyone?

|||

I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):

1. Click Start | Run and run dcomcnfg.exe

2. In MMC, open Component Services | My Computer

3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area

4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)

If that doesn't do the trick:

1. Start DCOMCNFG

2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab

3. In Launch and Activation Permissions box click Edit.

4. Add Network Service with Local Launch and Local Activation permissions

Good luck!|||Nope... you were right - neither worked.

I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?

Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!

|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||

I'm getting the same event error:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,

John

|||

Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.

Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.

Report server had previously been working ok until the final site config when the host headers were implemented.

As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.

All boxes running fine now.

The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.

|||

I believe this errors can be fixed by the following 899965

http://support.microsoft.com/?kbid=899965

|||

had a similar problem and found out the following:

If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config

Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>

The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....

and

<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...

Hope this helps,
Martin

PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx

|||Just a blank host header for a site that has an assigned IP. Work on three different boxes for me.|||Same for me. I moved Reporting Services to it's own website (because sharepoint installed it's own ISAPI filter on the default website that was totally screwing up RS), and set a host header for the new site. Every time I turned off the default website, RS would break, even though it should have nothing to do with it.
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.
|||

Folks,

I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.

If one of you could please set me right it would be much appreciated.

Firstly, the error I'm getting in the event log is as follows:

"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{BA126AD1-2166-11D1-B1D0-00805FC1270E}

to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."

So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:

HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}

Which identifies this as the "Network Connection Manager Class"

Allright..now the KB article then tells me to :

4. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.

If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program. 5. In Component Services, double-click Component Services, double-click Computers, double-click My Computer, and then click DCOM Config. 6. In the details pane, locate the program by using the friendly name.

If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.

And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)

So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....

I'd really appreciate a bit of help on this one...

Thanks

PJ

|||

Another question on this...just to be sure I'm chasing the right tail....

When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.

DCOM error

I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:

The request failed with HTTP status 400: Bad Request.

One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.

The new ReportServer VD appears to work (although no reports are installed to test).

Thanks for any help getting this issue resolved!

I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

Anyone?

|||

I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):

1. Click Start | Run and run dcomcnfg.exe

2. In MMC, open Component Services | My Computer

3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area

4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)

If that doesn't do the trick:

1. Start DCOMCNFG

2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab

3. In Launch and Activation Permissions box click Edit.

4. Add Network Service with Local Launch and Local Activation permissions

Good luck!

|||Nope... you were right - neither worked.

I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?

Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!

|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||

I'm getting the same event error:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,

John

|||

Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.

Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.

Report server had previously been working ok until the final site config when the host headers were implemented.

As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.

All boxes running fine now.

The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.

|||

I believe this errors can be fixed by the following 899965

http://support.microsoft.com/?kbid=899965

|||

had a similar problem and found out the following:

If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config

Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>

The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....

and

<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...

Hope this helps,
Martin

PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx

|||Just a blank host header for a site that has an assigned IP. Work on three different boxes for me.|||Same for me. I moved Reporting Services to it's own website (because sharepoint installed it's own ISAPI filter on the default website that was totally screwing up RS), and set a host header for the new site. Every time I turned off the default website, RS would break, even though it should have nothing to do with it.
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.
|||

Folks,

I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.

If one of you could please set me right it would be much appreciated.

Firstly, the error I'm getting in the event log is as follows:

"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{BA126AD1-2166-11D1-B1D0-00805FC1270E}

to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."

So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:

HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}

Which identifies this as the "Network Connection Manager Class"

Allright..now the KB article then tells me to :

4. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.

If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program. 5. In Component Services, double-click Component Services, double-click Computers, double-click My Computer, and then click DCOM Config. 6. In the details pane, locate the program by using the friendly name.

If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.

And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)

So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....

I'd really appreciate a bit of help on this one...

Thanks

PJ

|||

Another question on this...just to be sure I'm chasing the right tail....

When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.

DCOM error

I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:

The request failed with HTTP status 400: Bad Request.

One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.

The new ReportServer VD appears to work (although no reports are installed to test).

Thanks for any help getting this issue resolved!

I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

Anyone?

|||

I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):

1. Click Start | Run and run dcomcnfg.exe

2. In MMC, open Component Services | My Computer

3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area

4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)

If that doesn't do the trick:

1. Start DCOMCNFG

2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab

3. In Launch and Activation Permissions box click Edit.

4. Add Network Service with Local Launch and Local Activation permissions

Good luck!

|||Nope... you were right - neither worked.

I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?

Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||

I'm getting the same event error:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,

John

|||

Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.

Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.

Report server had previously been working ok until the final site config when the host headers were implemented.

As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.

All boxes running fine now.

The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.

|||

I believe this errors can be fixed by the following 899965

http://support.microsoft.com/?kbid=899965

|||

had a similar problem and found out the following:

If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config

Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>

The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....

and

<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...

Hope this helps,
Martin

PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx

|||Just a blank host header for a site that has an assigned IP. Work on three different boxes for me.|||Same for me. I moved Reporting Services to it's own website (because sharepoint installed it's own ISAPI filter on the default website that was totally screwing up RS), and set a host header for the new site. Every time I turned off the default website, RS would break, even though it should have nothing to do with it.
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.|||

Folks,

I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.

If one of you could please set me right it would be much appreciated.

Firstly, the error I'm getting in the event log is as follows:

"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{BA126AD1-2166-11D1-B1D0-00805FC1270E}

to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."

So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:

HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}

Which identifies this as the "Network Connection Manager Class"

Allright..now the KB article then tells me to :

4.

Click Start, click Run, type dcomcnfg in the Open box, and then click OK.

If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program.

5.

In Component Services, double-click Component Services, double-click Computers, double-click My Computer, and then click DCOM Config.

6.

In the details pane, locate the program by using the friendly name.

If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.

And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)

So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....

I'd really appreciate a bit of help on this one...

Thanks

PJ

|||

Another question on this...just to be sure I'm chasing the right tail....

When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.