cannot insert(urgent)

Hi
I am using Mssql2000 and i am facing a problem while saving data from my
application.
This Procedure is working fine.
CREATE PROCEDURE test(@.edate datetime,@.supp_idv char
(8),@.div char(2),@.dept
char(3))
AS CREATE table #oi(supp_id char(8),inv_no char
(15),inv_date datetime
NULL,dept_code char(3),dr numeric(14,3),cr numeric
(14,3),bal
numeric(14,3),tr_type char(2),entry_type char(1))
begin
Insert into #oi
select supp_id,inv_no,inv_date,dept_code,dr,cr,
(select sum(cr) - sum(dr) from ap_spled b WHERE
a.supp_id =
b.supp_id and
a.inv_no = b.inv_no group by
b.inv_no),trans_type,entry_type
from ap_spled a where division = @.div AND inv_date <=
@.edate and supp_id =
@.supp_idv and
(select sum(cr) - sum(dr) from ap_spled b WHERE
a.supp_id =
b.supp_id and a.inv_no = b.inv_no
group by b.inv_no) < 0 ;
select
a.supp_id,a.inv_no,a.inv_date,a.dr,a.cr,a.bal,a.tr
_type,a.entry_type,a.dept,d.name from #oi
a,supplier_file d
where a.supp_id = d.supplier_id order by supp_id,inv_no
The problem is that after retrieving this report in my application,if any
other user try to save from any other module ie
inserting any record into ap_spled table, the application is
hanging and cannot insert into this table.No users can save their data this
time, it is creating big prblm.This is the insert from my appl.This syntax is
also working fine if the procedure is not execute.
INSERT INTO ap_spled
(tr_no,entry_date,supp_id,dr_amt,cr_amt,trans_type ,acc_desc,division,dept_code,entry_type,inv_no,inv _date )
VALUES
(:tr,:dt,:sup,:dmt,:amt,'uu',:descr,:division,:dep t_code,:entry_type,:no,:vd
) ;
Is their any problem in my storedproced syntax or this is due to any table
locking pblm.How can i solve this.This is creating too much prbm
Please help.
Thanks
Thanks
On Fri, 11 Mar 2005 23:13:02 -0800, shif wrote:
(snip)
>The problem is that after retrieving this report in my application,if any
>other user try to save from any other module ie
>inserting any record into ap_spled table, the application is
>hanging and cannot insert into this table.
(snip)
Hi shif,
Some follow-up questions, the answers to which might help pinpoint the
poblem:
1. When this happens, how do you solve the issue? Wait until it goes
away (and if so, how long does that take, on average)? Close and restart
the application that tries to insert? Close and restart the application
that generated the report? Shutdown and restart the server? Or yet
something else?
2. Next time this happens, BEFORE taking any corrective action, open a
Query Analyzer window; type EXEC sp_who2; EXEC sp_lock and execute (make
sure that the "Results in Text" option is chosen), then copy the output
and paste it in a reply to this message.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This is the query analyser result after giving sp_who2 at the time of same
prblm.
And If I Close the application that generated the report,
the hanging will release and can save the data.
SPID Status Login HostName BlkBy DBName Command
CPUTime DiskIO LastBatch ProgramName SPID
-- -- -- -- -- --
-- -- -- -- -- --
1 sleeping sa . . NULL LOG WRITER
0 0 03/12 11:57:30 1
2 BACKGROUND sa . . master SIGNAL
HANDLER 0 0 03/12 11:57:30 2
3 BACKGROUND sa . . NULL LOCK
MONITOR 0 0 03/12 11:57:30 3
4 BACKGROUND sa . . NULL LAZY
WRITER 0 0 03/12 11:57:30 4
5 BACKGROUND sa . . master TASK
MANAGER 0 17 03/12 11:57:30 5
6 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 6
7 sleeping sa . . NULL CHECKPOINT
SLEEP 0 51 03/12 11:57:30 7
8 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 8
9 BACKGROUND sa . . master TASK
MANAGER 0 9 03/12 11:57:30 9
10 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 10
11 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 11
12 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 12
13 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 13
14 BACKGROUND sa . . master TASK
MANAGER 0 647 03/12 11:57:30 14
51 sleeping sa . . sns_acc AWAITING
COMMAND 5672 24 03/13 09:55:02 51
52 sleeping sa . . sns_acc AWAITING
COMMAND 2859 0 03/13 09:59:10 52
53 sleeping sa . . sns_acc AWAITING
COMMAND 2734 0 03/13 09:58:20 53
54 sleeping sa . . sns_acc AWAITING
COMMAND 11750 20 03/13 09:36:53 54
55 sleeping sa . . sns_acc AWAITING
COMMAND 4594 0 03/13 09:42:13 55
56 sleeping sa . . sns_acc AWAITING
COMMAND 2547 1 03/13 09:54:14 56
57 sleeping sa . . sns_acc AWAITING
COMMAND 4594 194 03/13 09:50:06 57
58 sleeping sa . . sns_acc AWAITING
COMMAND 5500 0 03/13 09:58:07 58
59 sleeping sa . . sns_acc AWAITING
COMMAND 5266 0 03/13 09:14:18 59
60 sleeping sa . . sns_acc AWAITING
COMMAND 8875 0 03/13 09:13:22 60
61 sleeping sa . . sns_acc AWAITING
COMMAND 9000 0 03/13 09:15:11 61
62 sleeping sa . . sns_acc AWAITING
COMMAND 4594 0 03/13 09:42:33 62
63 sleeping sa NAZAR . sns_acc AWAITING
COMMAND 5344 0 03/13 09:55:02 MS SQLEM 63
64 sleeping sa . 66 sns_acc SELECT
3031 0 03/13 10:00:11 64
65 RUNNABLE sa NAZAR . sns_acc SELECT
INTO 4594 11 03/13 09:49:33 SQL Query Analyzer 65
66 sleeping sa . 58 sns_acc INSERT
5719 0 03/13 09:59:55 66
67 sleeping sa . . sns_acc AWAITING
COMMAND 5297 0 03/13 09:52:47 67
"Hugo Kornelis" wrote:

> On Fri, 11 Mar 2005 23:13:02 -0800, shif wrote:
> (snip)
> (snip)
> Hi shif,
> Some follow-up questions, the answers to which might help pinpoint the
> poblem:
> 1. When this happens, how do you solve the issue? Wait until it goes
> away (and if so, how long does that take, on average)? Close and restart
> the application that tries to insert? Close and restart the application
> that generated the report? Shutdown and restart the server? Or yet
> something else?
> 2. Next time this happens, BEFORE taking any corrective action, open a
> Query Analyzer window; type EXEC sp_who2; EXEC sp_lock and execute (make
> sure that the "Results in Text" option is chosen), then copy the output
> and paste it in a reply to this message.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Sat, 12 Mar 2005 22:23:01 -0800, shif wrote:

>This is the query analyser result after giving sp_who2 at the time of same
>prblm.
>And If I Close the application that generated the report,
>the hanging will release and can save the data.
(snip)
Hi shif,
Thanks for posting this. Have a look at these three rows:

>SPID Status Login HostName BlkBy DBName Command
> CPUTime DiskIO LastBatch ProgramName SPID
>-- -- -- -- -- --
>-- -- -- -- -- --
(snip)
>58 sleeping sa . . sns_acc AWAITING
>COMMAND 5500 0 03/13 09:58:07 58
(...)
>64 sleeping sa . 66 sns_acc SELECT
> 3031 0 03/13 10:00:11 64
(...)
>66 sleeping sa . 58 sns_acc INSERT
> 5719 0 03/13 09:59:55 66
As you can see (even easier if you edit them to merge the split lines
again), the connection with SPID 64 is blocked by (column BlkBy) SPID
66, which is in turn blocked by connection 58.
Unfortunately, you didn't post the output from sp_lock. But I guess that
this would show that either SPID 66 or (more likely) SPID 58 is used for
generating the report, but still holds some locks that are also required
for the insert.
My guess, at this point, would be that the code that generated the
report opens a transaction, but fails to close it. In the transaction,
either some data gets updated, or a non-standard isolation level is used
to maintain locks on data being read. While the acquiring of the locks
might be legitimate in the context of the report generation, they should
really be released when the report is finished. My suspicion is that
this doesn't happen, because the report generating code fails to execute
an COMMIT TRANSACTION command.
When you close the application, the connection gets broken and SQL
Server will rollback the transaction and then release the locks. Then,
your other connection will get the locks they required and were wating
for; they'll continue to run - until you re-run the report.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi sir
I forget the sp_lock i am adding here and it is lengthy upto 450 rows i
deleted some rows.if i have your mail id i can mail you the full text file.
One more thing i forgot to tell is ,actualy the database which i am using
was working in mssql7.I was just attached to ms2000 srvr,now i exported all
data into a new database in same server,But again i saw today again from same
screen not save but hanging one time in new db.here is the sp_who2 and sp_lock
..
sp_lock
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 2 3 2 KEY (9c01d388586f) X GRANT
51 2 2 1 KEY (590093cc6231) X GRANT
52 8 0 0 DB S GRANT
53 8 0 0 DB S GRANT
54 8 0 0 DB S GRANT
55 8 0 0 DB S GRANT
56 8 0 0 DB S GRANT
56 2 6 0 TAB IX GRANT
56 2 2 0 TAB IX GRANT
56 2 1 0 TAB IX GRANT
56 2 3 1 KEY (7d00de4e4040) X GRANT
56 2 3 1 KEY (8000885e2318) X GRANT
56 8 1410104064 0 TAB S GRANT
56 2 3 1 KEY (810066f1960a) X GRANT
56 2 3 2 KEY (120262fbbe82) X GRANT
56 2 3 2 KEY (b901f41a78b7) X GRANT
56 2 3 2 KEY (a30233242999) X GRANT
56 2 3 2 KEY (a701bbd2b59b) X GRANT
56 2 3 1 KEY (820003962ab2) X GRANT
56 2 0 0 IDX IDX: 2:100335883 X GRANT
56 2 1003358839 0 TAB Sch-M GRANT
56 2 3 1 KEY (7e00bb29fcf8) X GRANT
56 2 3 2 KEY (c401f8c9de07) X GRANT
56 2 3 2 KEY (ad024e966d4d) X GRANT
57 2 1 2 KEY (b516cfe74d67) X GRANT
57 2 3 1 KEY (ef00eaadae35) X GRANT
57 2 3 2 KEY (9c020770e7da) X GRANT
57 2 2 1 KEY (e80092c5f44b) X GRANT
57 2 0 0 IDX IDX: 2:891358440 X GRANT
57 2 891358440 0 TAB Sch-M GRANT
57 2 3 2 KEY (a602bbabc534) X GRANT
57 2 3 1 KEY (ed006165a79f) X GRANT
57 2 3 1 KEY (f100d9da71d5) X GRANT
57 2 3 2 KEY (9f0226e43aa2) X GRANT
57 2 3 2 KEY (0e02b8060e3b) X GRANT
57 2 1 3 KEY (e800e561205a) X GRANT
57 2 3 1 KEY (f000bcbdcd6d) X GRANT
57 2 3 1 KEY (ec0004021b27) X GRANT
57 2 3 1 KEY (ee008fca128d) X GRANT
57 2 3 1 KEY (f2003775c4c7) X GRANT
57 2 3 2 KEY (9602c76d7c54) X GRANT
57 2 99 0 RID 1:78:1 X GRANT
57 2 3 1 KEY (ea00d85d7002) X GRANT
57 2 3 2 KEY (12035d1d7fdb) X GRANT
57 2 3 2 KEY (2b02dc4b8826) X GRANT
57 2 3 2 KEY (1e033df41ec8) X GRANT
57 2 3 1 KEY (eb00bd3accba) X GRANT
57 2 1 1 KEY (e8009061461e) X GRANT
57 2 3 2 KEY (35023d7f78d0) X GRANT
57 2 0 0 PAG 1:88 X GRANT
57 2 9 0 TAB IX GRANT
57 2 12 0 TAB IX GRANT
57 2 11 0 TAB IX GRANT
57 2 99 0 RID 1:78:0 X GRANT
57 2 0 0 PAG 1:78 X GRANT
57 2 0 0 PAG 1:77 X GRANT
57 2 0 0 EXT 1:88 X GRANT
58 2 3 1 KEY (89008d396180) X GRANT
58 2 3 2 KEY (5e01e45197c4) X GRANT
58 2 3 1 KEY (85003586b7ca) X GRANT
58 2 3 2 KEY (e601a49ec50f) X GRANT
58 2 3 1 KEY (35005b77329d) X GRANT
58 2 99 0 RID 1:121:0 X GRANT
58 2 99 0 RID 1:123:0 X GRANT
58 2 3 1 KEY (3100e3c8e4d7) X GRANT
58 2 0 0 PAG 1:121 X GRANT
58 2 3 1 KEY (4d00badd2f69) X GRANT
58 2 2 1 KEY (49007dcee1a2) X GRANT
58 2 99 0 RID 1:252:0 X GRANT
58 2 0 0 PAG 1:252 X GRANT
58 2 3 1 KEY (4c0003e5f8f4) X GRANT
58 2 3 1 KEY (4f00311526c3) X GRANT
58 2 3 1 KEY (4e00dfba93d1) X GRANT
58 2 3 1 KEY (530089aaf089) X GRANT
58 2 3 1 KEY (52006705459b) X GRANT
58 2 3 1 KEY (3a0048a3eb2f) X GRANT
58 2 3 2 KEY (000231e2855c) X GRANT
58 2 3 2 KEY (6f01f7b5e64f) X GRANT
58 2 3 2 KEY (57026d5bbde3) X GRANT
58 2 3 2 KEY (7a01dcdc9a6a) X GRANT
58 2 3 2 KEY (5b0251f72f39) X GRANT
58 2 3 1 KEY (3600f01c3d65) X GRANT
58 2 3 2 KEY (3602a72cacc0) X GRANT
58 2 622833481 0 TAB Sch-M GRANT
58 2 494833025 0 TAB Sch-M GRANT
58 2 1 3 KEY (4900a5f7bb28) X GRANT
58 2 3 1 KEY (51000262f923) X GRANT
58 2 3 2 KEY (590270009824) X GRANT
58 2 3 2 KEY (b001ef43c30a) X GRANT
58 2 3 1 KEY (3700d0bf3b37) X GRANT
58 2 3 1 KEY (320086af586f) X GRANT
58 2 3 1 KEY (36003e108e25) X GRANT
58 2 3 1 KEY (8a00e85edd38) X GRANT
58 2 3 1 KEY (8700be4ebe60) X GRANT
58 2 3 1 KEY (84008cbe6057) X GRANT
58 2 3 1 KEY (8b0006f1682a) X GRANT
58 2 3 1 KEY (30005af0334a) X GRANT
58 2 3 1 KEY (33006800ed7d) X GRANT
58 2 1 1 KEY (4900d0f7dd6c) X GRANT
58 2 3 2 KEY (7502b83841ec) X GRANT
58 2 3 2 KEY (ce016cf265f3) X GRANT
58 2 3 2 KEY (8e01a236c901) X GRANT
58 2 3 1 KEY (3200a78b5fea) X GRANT
58 2 3 2 KEY (720182f319f5) X GRANT
58 2 3 2 KEY (740139cfb2a2) X GRANT
58 2 3 1 KEY (3700a60c5e3d) X GRANT
58 2 3 1 KEY (30002c435640) X GRANT
58 2 3 1 KEY (3100c2ece352) X GRANT
58 2 3 2 KEY (8802393b156f) X GRANT
58 2 3 1 KEY (33001eb38877) X GRANT
58 2 2 1 KEY (2d000211c41d) X GRANT
58 2 3 2 KEY (ad02bb7a3e55) X GRANT
58 2 1 3 KEY (2d00f2931699) X GRANT
58 2 2 1 KEY (2f00d83b7a82) X GRANT
58 2 3 2 KEY (71010a60f6ca) X GRANT
58 2 3 2 KEY (65022c456bed) X GRANT
58 2 3 2 KEY (1c0296c64325) X GRANT
58 2 1 2 KEY (a416b6eaa566) X GRANT
58 2 3 2 KEY (5f0124fb5aa1) X GRANT
58 2 3 2 KEY (b702c6c87a81) X GRANT
58 2 3 2 KEY (7302a563642b) X GRANT
58 2 3 2 KEY (5c015f6d3c93) X GRANT
58 2 1 3 KEY (2f00b3d3188c) X GRANT
58 2 3 2 KEY (5002af3c2995) X GRANT
58 2 3 2 KEY (78017fa8ec67) X GRANT
58 2 3 2 KEY (38025c196d65) X GRANT
58 2 3 1 KEY (5400eccd4c31) X GRANT
58 2 3 1 KEY (4b006682444c) X GRANT
58 2 3 2 KEY (59024cac0afe) X GRANT
58 2 686833709 0 TAB Sch-M GRANT
58 2 0 0 IDX IDX: 2:686833709 X GRANT
58 2 3 1 KEY (2e00d1383ae0) X GRANT
58 2 3 2 KEY (b1012fe90e6f) X GRANT
58 2 3 2 KEY (3402a73ce927) X GRANT
58 2 1 1 KEY (8100cf4109b0) X GRANT
58 2 3 1 KEY (4a00882df15e) X GRANT
58 2 3 1 KEY (500054729a7b) X GRANT
58 2 1 3 KEY (8100ba416ff4) X GRANT
58 2 3 2 KEY (c60132dde66c) X GRANT
58 2 2 1 KEY (81008e0ab94d) X GRANT
58 2 3 2 KEY (63020db2dcf0) X GRANT
58 2 3 1 KEY (39002dc45797) X GRANT
58 2 3 1 KEY (3800c36be285) X GRANT
58 2 3 2 KEY (e401beea3b50) X GRANT
58 2 1 1 KEY (2f00c6d37ec8) X GRANT
58 2 3 1 KEY (8300e9d9dcef) X GRANT
58 2 3 1 KEY (8800db2902d8) X GRANT
58 2 3 1 KEY (8c006396d492) X GRANT
58 2 3 2 KEY (9601fc194a9e) X GRANT
58 2 3 2 KEY (c30122edca89) X GRANT
58 2 3 2 KEY (8b0171b6b1e4) X GRANT
58 2 3 1 KEY (34000d6751c5) X GRANT
58 2 3 1 KEY (2f003f978ff2) X GRANT
58 2 3 1 KEY (3800b5d8878f) X GRANT
58 2 3 2 KEY (7f02c58a0538) X GRANT
58 2 3 1 KEY (8200077669fd) X GRANT
58 2 3 2 KEY (ab02a6211b92) X GRANT
59 2 3 2 KEY (530262013702) X GRANT
59 2 3 2 KEY (ee01a3dbd3c6) X GRANT
59 2 3 1 KEY (c20049df58db) X GRANT
59 2 99 0 RID 1:83:4 X GRANT
59 2 3 1 KEY (c600f1608e91) X GRANT
59 2 3 1 KEY (c000958033fe) X GRANT
59 2 3 2 KEY (e202d8f3d456) X GRANT
59 2 1166835419 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:116683541 X GRANT
59 2 3 2 KEY (480202cea6d6) X GRANT
59 2 3 2 KEY (2d02ce0794e9) X GRANT
59 2 3 2 KEY (e7014fa399f4) X GRANT
59 2 3 2 KEY (2002e139c422) X GRANT
59 2 3 2 KEY (f4023e1e8569) X GRANT
59 2 3 1 KEY (df006775fe0b) X GRANT
59 2 3 1 KEY (2400ca2649fa) X GRANT
59 2 3 2 KEY (ed0163711ea3) X GRANT
59 2 0 0 IDX IDX: 2:108683513 X GRANT
59 2 1086835134 0 TAB Sch-M GRANT
59 2 2 1 KEY (db00b6aaf8a1) X GRANT
59 2 1 1 KEY (7600d23c06ae) X GRANT
59 2 3 1 KEY (280072999fb0) X GRANT
59 2 3 1 KEY (dc005585203c) X GRANT
59 2 3 1 KEY (e300dfca2841) X GRANT
59 2 3 2 KEY (75023f81917e) X GRANT
59 2 3 1 KEY (dd00bb2a952e) X GRANT
59 2 99 0 RID 1:83:3 X GRANT
59 2 99 0 RID 1:265:3 X GRANT
59 2 3 2 KEY (76022a829f89) X GRANT
59 2 3 2 KEY (c502d70311df) X GRANT
59 2 1054835020 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:105483502 X GRANT
59 2 0 0 IDX IDX: 2:587357357 X GRANT
59 2 99 0 RID 1:265:4 X GRANT
59 2 3 2 KEY (05034890c140) X GRANT
59 2 3 2 KEY (ea0243acc1bd) X GRANT
59 2 3 2 KEY (b801aadcc2bb) X GRANT
59 2 3 1 KEY (4f0081114225) X GRANT
59 2 3 2 KEY (9901c89022fd) X GRANT
59 2 3 2 KEY (6b0113c06a55) X GRANT
59 2 1 3 KEY (7600a73c60ea) X GRANT
59 2 3 2 KEY (dc0182a7d165) X GRANT
59 2 3 1 KEY (81008ec31911) X GRANT
59 2 587357357 0 TAB Sch-M GRANT
59 2 3 2 KEY (d5017a82f3c9) X GRANT
59 2 3 1 KEY (7d00367ccf5b) X GRANT
59 2 3 1 KEY (7700ea23a47e) X GRANT
59 2 3 2 KEY (48022e9d1de7) X GRANT
59 2 3 2 KEY (8202c9f8391e) X GRANT
59 2 3 1 KEY (af005c6d402e) X GRANT
59 2 3 1 KEY (b4006e9d9e19) X GRANT
59 2 3 1 KEY (ae00b2c2f53c) X GRANT
59 2 3 1 KEY (b800d6224853) X GRANT
59 2 1 3 KEY (db000c3967a4) X GRANT
59 2 3 1 KEY (4d000ad94b8f) X GRANT
59 2 1 1 KEY (ad001f254d94) X GRANT
59 2 99 0 RID 1:83:5 X GRANT
59 2 3 2 KEY (11032879a053) X GRANT
59 2 1 3 KEY (be00e9b4690b) X GRANT
59 2 3 2 KEY (7d023b626dd6) X GRANT
59 2 3 2 KEY (4a021f958311) X GRANT
59 2 99 0 RID 1:265:5 X GRANT
59 2 3 2 KEY (0b02e0c0b85a) X GRANT
59 2 3 2 KEY (0002fcd2ea7e) X GRANT
59 2 3 2 KEY (1d0212a37d37) X GRANT
59 2 3 2 KEY (e302a4bc0768) X GRANT
59 2 3 2 KEY (6402e44233a7) X GRANT
59 2 99 0 RID 1:83:6 X GRANT
59 2 1 2 KEY (a516e15fb233) X GRANT
59 2 3 2 KEY (a0020c139ab2) X GRANT
59 2 3 2 KEY (7c018b8b4961) X GRANT
59 2 3 2 KEY (d902d90e43bc) X GRANT
59 2 3 1 KEY (27009c362aa2) X GRANT
59 2 3 2 KEY (7602a911580d) X GRANT
59 2 3 2 KEY (4e0150db01c9) X GRANT
59 2 3 1 KEY (2100f8d697cd) X GRANT
59 2 3 1 KEY (23002489fce8) X GRANT
59 2 2 1 KEY (4c003c6ce3ea) X GRANT
59 2 3 2 KEY (f2015f39f403) X GRANT
59 2 99 0 RID 1:265:6 X GRANT
59 2 1 3 KEY (4c00df72296c) X GRANT
59 2 3 2 KEY (7b014b218404) X GRANT
59 2 3 2 KEY (b901b05ddd89) X GRANT
59 2 3 2 KEY (c3011dd2e151) X GRANT
59 2 3 1 KEY (20009db12b75) X GRANT
59 2 3 2 KEY (8e01e64c5a6a) X GRANT
59 2 3 1 KEY (2600f951961a) X GRANT
59 2 3 1 KEY (e100ecbdf7a1) X GRANT
59 2 3 1 KEY (e500540221eb) X GRANT
59 2 3 1 KEY (e400baad94f9) X GRANT
59 2 3 1 KEY (220041ee4050) X GRANT
59 2 3 1 KEY (e000021242b3) X GRANT
59 2 99 0 RID 1:83:7 X GRANT
59 2 1 3 KEY (1e00e99c369e) X GRANT
59 2 3 1 KEY (4e00e476fe9d) X GRANT
59 2 3 2 KEY (7802b44a7dca) X GRANT
59 2 3 2 KEY (ac026cfafba1) X GRANT
59 2 3 2 KEY (d702c455667b) X GRANT
59 2 475356958 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:475356958 X GRANT
59 2 3 1 KEY (c3002cb8e463) X GRANT
59 2 3 2 KEY (ef010149377d) X GRANT
59 2 3 1 KEY (c100f0e78f46) X GRANT
59 2 3 1 KEY (c70094073229) X GRANT
59 2 3 1 KEY (e20089da4b19) X GRANT
59 2 99 0 RID 1:265:7 X GRANT
59 2 3 2 KEY (a6015ec98acd) X GRANT
59 2 3 1 KEY (e60031659d53) X GRANT
59 2 3 2 KEY (0b02fc0d2c21) X GRANT
59 2 3 1 KEY (7c00531b73e3) X GRANT
59 2 3 1 KEY (8000eba4a5a9) X GRANT
59 2 3 1 KEY (c400c2175171) X GRANT
59 2 1 1 KEY (4c00aa724f28) X GRANT
59 2 3 2 KEY (5902f55ebf3e) X GRANT
59 2 99 0 RID 1:255:1 X GRANT
59 2 3 1 KEY (c8007aa8873b) X GRANT
59 2 3 2 KEY (dd01420d1c00) X GRANT
59 2 99 0 RID 1:265:1 X GRANT
59 2 3 1 KEY (b3000bfa22a1) X GRANT
59 2 3 1 KEY (b000390afc96) X GRANT
59 2 3 1 KEY (b6005dea41f9) X GRANT
59 2 3 2 KEY (9202e05db1c9) X GRANT
59 2 3 1 KEY (b700b345f4eb) X GRANT
59 2 3 2 KEY (a2021148bf75) X GRANT
59 2 0 0 EXT 1:272 X GRANT
59 2 3 1 KEY (b500388dfd41) X GRANT
59 2 3 2 KEY (2802bf1647bd) X GRANT
59 2 3 1 KEY (b10080322b0b) X GRANT
59 2 3 1 KEY (2500af41f542) X GRANT
59 2 3 1 KEY (1f00731e9e67) X GRANT
59 2 3 1 KEY (290017fe2308) X GRANT
59 2 3 2 KEY (25024f5e16cb) X GRANT
59 2 3 1 KEY (57006e39f6e0) X GRANT
59 2 3 1 KEY (5300d68620aa) X GRANT
59 2 3 2 KEY (0302beef3bc5) X GRANT
59 2 3 2 KEY (110204d8baa9) X GRANT
59 2 859358326 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:859358326 X GRANT
59 2 99 0 RID 1:83:1 X GRANT
59 2 2 1 KEY (1e0029c04d24) X GRANT
59 2 2 1 KEY (ad00944e5e86) X GRANT
59 2 3 2 KEY (070355cbe487) X GRANT
59 2 3 1 KEY (de00de4d2996) X GRANT
59 2 3 2 KEY (b402ab55b0bf) X GRANT
59 2 3 1 KEY (5000382995b8) X GRANT
59 2 3 1 KEY (5400809643f2) X GRANT
59 2 3 2 KEY (e8025ef7e47a) X GRANT
59 2 3 2 KEY (54026227c7c5) X GRANT
59 2 2 1 KEY (be008cfdf811) X GRANT
59 2 3 1 KEY (7a00d8d37a49) X GRANT
59 2 3 1 KEY (7800048c116c) X GRANT
59 2 3 2 KEY (6001da66a88a) X GRANT
59 2 3 1 KEY (7e00606cac03) X GRANT
59 2 3 2 KEY (fa010116779c) X GRANT
59 2 99 0 RID 1:265:2 X GRANT
59 2 3 1 KEY (56000b5e4a58) X GRANT
59 2 1 1 KEY (be009cb40f4f) X GRANT
59 2 3 1 KEY (bf007b2f86ec) X GRANT
59 2 3 2 KEY (0a023ca7e144) X GRANT
59 2 3 2 KEY (4d019071ccac) X GRANT
59 2 3 1 KEY (5200b3e19c12) X GRANT
59 2 3 1 KEY (c500a770edc9) X GRANT
59 2 99 0 RID 1:83:2 X GRANT
59 2 3 1 KEY (c9001fcf3b83) X GRANT
59 2 3 1 KEY (b200e55597b3) X GRANT
59 2 1 3 KEY (ad006a252bd0) X GRANT
59 2 3 2 KEY (bb0143fd62ce) X GRANT
59 2 1 1 KEY (db00793901e0) X GRANT
59 2 3 1 KEY (7f00050b10bb) X GRANT
59 2 3 2 KEY (0302857cb7b4) X GRANT
59 2 3 1 KEY (7b00bdb4c6f1) X GRANT
59 2 3 1 KEY (790061ebadd4) X GRANT
59 2 0 0 PAG 1:255 X GRANT
59 2 0 0 PAG 1:254 X GRANT
59 2 99 0 RID 1:255:0 X GRANT
59 2 2 1 KEY (76001c75619c) X GRANT
59 2 99 0 RID 1:249:0 X GRANT
59 2 3 2 KEY (910196bfa162) X GRANT
59 2 0 0 PAG 1:193 X GRANT
59 2 0 0 PAG 1:199 X GRANT
59 2 0 0 PAG 1:266 X GRANT
59 2 0 0 PAG 1:267 X GRANT
59 2 0 0 PAG 1:272 X GRANT
59 2 0 0 PAG 1:270 X GRANT
59 2 0 0 PAG 1:271 X GRANT
59 2 3 2 KEY (63014defe9ca) X GRANT
59 2 1 1 KEY (1e009c9c50da) X GRANT
59 2 3 2 KEY (a5019e6347a8) X GRANT
59 2 0 0 PAG 1:86 X GRANT
59 2 1 0 TAB IX GRANT
59 2 3 0 TAB IX GRANT
59 2 2 0 TAB IX GRANT
59 2 6 0 TAB IX GRANT
59 8 0 0 DB S GRANT
60 8 0 0 DB S GRANT
61 8 0 0 DB S GRANT
61 8 1410104064 0 TAB IX WAIT
62 1 85575343 0 TAB IS GRANT
sp_who
SPID Status Login HostName BlkBy DBName Command
CPUTime DiskIO LastBatch ProgramName SPID
-- -- -- -- -- --
-- -- -- -- -- --
1 sleeping sa . . NULL LOG
WRITER 31 0 03/12 11:57:30 1
2 BACKGROUND sa . . master SIGNAL
HANDLER 0 0 03/12 11:57:30 2
3 BACKGROUND sa . . NULL LOCK
MONITOR 0 0 03/12 11:57:30 3
4 BACKGROUND sa . . NULL LAZY
WRITER 0 0 03/12 11:57:30 4
5 BACKGROUND sa . . master TASK
MANAGER 0 55 03/12 11:57:30 5
6 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 6
7 sleeping sa . . NULL
CHECKPOINT SLEEP 219 246 03/12 11:57:30 7
8 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 8
9 BACKGROUND sa . . master TASK
MANAGER 0 619 03/12 11:57:30 9
10 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 10
11 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 11
12 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 12
13 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 13
14 BACKGROUND sa . . master TASK
MANAGER 0 652 03/12 11:57:30 14
51 sleeping sa . . sns_acc2 AWAITING
COMMAND 11094 23 03/14 10:17:27 51
52 sleeping sa . . sns_acc2 AWAITING
COMMAND 4734 29 03/14 10:36:50 52
53 sleeping sa . . sns_acc2 AWAITING
COMMAND 13875 340 03/14 10:36:14 53
54 sleeping sa . . sns_acc2 AWAITING
COMMAND 23344 0 03/14 09:47:44 54
55 sleeping sa . . sns_acc2 AWAITING
COMMAND 1125 0 03/14 10:36:37 55
56 sleeping sa . . sns_acc2 AWAITING
COMMAND 14906 25 03/14 10:28:29 56
57 sleeping sa . . sns_acc2 AWAITING
COMMAND 15781 25 03/14 10:21:47 57
58 sleeping sa . . sns_acc2 AWAITING
COMMAND 5688 4 03/14 09:09:35 58
59 sleeping sa . . sns_acc2 AWAITING
COMMAND 6281 0 03/14 10:21:41 59
60 sleeping sa . . sns_acc2 AWAITING
COMMAND 17875 0 03/14 10:23:16 60
61 sleeping sa . 56 sns_acc2 INSERT
7516 0 03/14 10:35:18 61
62 RUNNABLE sa NAZAR . master SELECT
INTO 8719 0 03/14 10:36:30 SQL Query Analyzer 62
Please give me a solution to solve this and your email id pls.

> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Sun, 13 Mar 2005 23:19:02 -0800, shif wrote:

>Hi sir
>I forget the sp_lock i am adding here and it is lengthy upto 450 rows i
>deleted some rows.
Hi shif,
Thanks for posting the sp_lock output. Even though you deleted some
rows, I think you've left in the ones that count.

>if i have your mail id i can mail you the full text file.
Normally, I prefer to keep newsgroup help in the groups and reserve my
e-mail box for my loving mother, the usual load of spam and paying
customers. But I understand you don't want to post the full output here,
so go ahead - I'll allow it this time. My e-mail address is in the
headers of all my messages - you'll have to remove "_NO_" and "_SPAM_"
before using it (they are included to confuse the spam-bots).

>here is the sp_who2 and sp_lock
This output is even more clear that the previous output. Let's start
with the sp_who2 output. This row:

>SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
>-- -- -- -- -- -- -- -- -- -- -- --
(snip)
>61 sleeping sa . 56 sns_acc2 INSERT 7516 0 03/14 10:35:18 61
shows that connection with SPID 61 is attempting an insert, but has to
wait. It is apparently attempting to acquire a lock on a resource that
is already locked by another application - one that currently holds SPID
56, as the entry "56" in the BlkBy columns proves.
And this row:
>SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
>-- -- -- -- -- -- -- -- -- -- -- --
(snip)
>56 sleeping sa . . sns_acc2 AWAITING COMMAND 14906 25 03/14 10:28:29 56
shows that the blocking connection is currently not doing anything.
Now, let's take at the entries in the sp_lock output that belong to SPID
61:

>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
(snip)
>61 8 0 0 DB S GRANT
>61 8 1410104064 0 TAB IX WAIT
As you see, two locks only. One is granted, one is waiting. This one is
the cause of the blocking. It's a table lock, and the mode is IX
(intent-exclusive) - that means that the application needs to acquire an
exclusive lock on either one page or one row that's part of a table.
Since locks can always be upgraded to table locks, the right to do this
has to be reserved to prevent deadlocking.
To find out which table, use the values in dbid and ObjId. First, use
dbid to identify the database name:
SELECT db_name(8)
Then switch to the database returned by the previous query, and find the
name of the offending table:
USE <db-name goes here>
GO
SELECT object_name(1410104064)
My guess is that you'll find the database to be sns_acc2 and the table
to be ap_spled.
Finally, let's inspect the sp_lock output belonging to the blocking SPID
56. There's a whole lot of rows there. Most of them in dbid 2 (tempdb on
most, maybe even all installations). Only one in dbid 8, but it has the
same ObjId as in the previous row:

>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
(snip)
>56 8 1410104064 0 TAB S GRANT
And this one is the culprit. It's a shared table lock on the same table
that the insert statement needs to get an intent-exclusive lock on. A
shared lock is normally used for reading data - it allows others to read
simultaneously, but it disallows exclusive locks (used for data
modification), so that the data being read can't change. For obvious
reasons, the shared lock on this table is not compatible with the
requested intent-exclusive lock, so SPID 61 is put in a wait state until
the lock is released. For some reason, his doesn't happen. Until you
close the report generating application - then, either the application
closes the connection (committing or rolling back any open transactions)
or the connection gets dropped (incurring a forced rollback of the open
transactions). All locks held by SPID 56 are then released, and the
inserting applications finaly gets the lock it has been witing for :-)
The next step should be to inspect the code of the application that
generates the report. SQL Server's default behaviour is to release
shared locks directly after the SELECT statement has finished executing.
But that default behaviour can be changed. For a report generation that
queries the same table several times, it might be necessary to prevent
any modification between those queries, to prevent inconsistensies in
the report (simple example: report debit first, then report credit - if
any booking is inserted in between, the debit total won't match the
credit total and your bookkeeper will have a fit). In such cases, the
transaction isolation level is increased to either repeatable read or
serializable (this forces SQL Server to hold on to shared locks until
the transaction is finished). A transaction is started and all queries
needed to generate the report are run. The first query will acquire the
shared locks; they will be kept, so that no data can change until all
queries are finished. When the report is complete, the transaction is
committed, all locks are released and the report is presented to the
user (or printed, or stored, or whatever).
In your case, you should look for transactions that are started (with
BEGIN TRANSACTION), but not ended (with either ROLLBACK TRANSACTION or
COMMIT TRANSACTION). Note that some settings can cause an implicit BEGIN
TRANSACTION. Also note that the process should not need any user input
between the start and the end of the transaction, as users tend to go
away from the computer and drink some coffee, unaware that their failure
to click "OK" causes their collegues to be unable to work.
I hope the above is helpful in determining and fixing your problem!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||HI
Again back,actualy the problem was in the stored procedure i am using an
update stmt,i checked by removing the update stmt
and the hanginh prblm for 2 days not happend hope it's ok and
thanks for your help.another thing if i want to include the update
stsmt in the procedure like this What i have to do.
update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no
from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT'
OR #pd_temp.trans_type='PV')
This stmt is creating prblm,can i give any commit here.Can you please help
with a sample stmt.
Thank you
"Hugo Kornelis" wrote:

> On Sun, 13 Mar 2005 23:19:02 -0800, shif wrote:
>
> Hi shif,
> Thanks for posting the sp_lock output. Even though you deleted some
> rows, I think you've left in the ones that count.
>
> Normally, I prefer to keep newsgroup help in the groups and reserve my
> e-mail box for my loving mother, the usual load of spam and paying
> customers. But I understand you don't want to post the full output here,
> so go ahead - I'll allow it this time. My e-mail address is in the
> headers of all my messages - you'll have to remove "_NO_" and "_SPAM_"
> before using it (they are included to confuse the spam-bots).
>
> This output is even more clear that the previous output. Let's start
> with the sp_who2 output. This row:
> (snip)
> shows that connection with SPID 61 is attempting an insert, but has to
> wait. It is apparently attempting to acquire a lock on a resource that
> is already locked by another application - one that currently holds SPID
> 56, as the entry "56" in the BlkBy columns proves.
> And this row:
> (snip)
> shows that the blocking connection is currently not doing anything.
> Now, let's take at the entries in the sp_lock output that belong to SPID
> 61:
> (snip)
> As you see, two locks only. One is granted, one is waiting. This one is
> the cause of the blocking. It's a table lock, and the mode is IX
> (intent-exclusive) - that means that the application needs to acquire an
> exclusive lock on either one page or one row that's part of a table.
> Since locks can always be upgraded to table locks, the right to do this
> has to be reserved to prevent deadlocking.
> To find out which table, use the values in dbid and ObjId. First, use
> dbid to identify the database name:
> SELECT db_name(8)
> Then switch to the database returned by the previous query, and find the
> name of the offending table:
> USE <db-name goes here>
> GO
> SELECT object_name(1410104064)
> My guess is that you'll find the database to be sns_acc2 and the table
> to be ap_spled.
> Finally, let's inspect the sp_lock output belonging to the blocking SPID
> 56. There's a whole lot of rows there. Most of them in dbid 2 (tempdb on
> most, maybe even all installations). Only one in dbid 8, but it has the
> same ObjId as in the previous row:
> (snip)
> And this one is the culprit. It's a shared table lock on the same table
> that the insert statement needs to get an intent-exclusive lock on. A
> shared lock is normally used for reading data - it allows others to read
> simultaneously, but it disallows exclusive locks (used for data
> modification), so that the data being read can't change. For obvious
> reasons, the shared lock on this table is not compatible with the
> requested intent-exclusive lock, so SPID 61 is put in a wait state until
> the lock is released. For some reason, his doesn't happen. Until you
> close the report generating application - then, either the application
> closes the connection (committing or rolling back any open transactions)
> or the connection gets dropped (incurring a forced rollback of the open
> transactions). All locks held by SPID 56 are then released, and the
> inserting applications finaly gets the lock it has been witing for :-)
> The next step should be to inspect the code of the application that
> generates the report. SQL Server's default behaviour is to release
> shared locks directly after the SELECT statement has finished executing.
> But that default behaviour can be changed. For a report generation that
> queries the same table several times, it might be necessary to prevent
> any modification between those queries, to prevent inconsistensies in
> the report (simple example: report debit first, then report credit - if
> any booking is inserted in between, the debit total won't match the
> credit total and your bookkeeper will have a fit). In such cases, the
> transaction isolation level is increased to either repeatable read or
> serializable (this forces SQL Server to hold on to shared locks until
> the transaction is finished). A transaction is started and all queries
> needed to generate the report are run. The first query will acquire the
> shared locks; they will be kept, so that no data can change until all
> queries are finished. When the report is complete, the transaction is
> committed, all locks are released and the report is presented to the
> user (or printed, or stored, or whatever).
> In your case, you should look for transactions that are started (with
> BEGIN TRANSACTION), but not ended (with either ROLLBACK TRANSACTION or
> COMMIT TRANSACTION). Note that some settings can cause an implicit BEGIN
> TRANSACTION. Also note that the process should not need any user input
> between the start and the end of the transaction, as users tend to go
> away from the computer and drink some coffee, unaware that their failure
> to click "OK" causes their collegues to be unable to work.
> I hope the above is helpful in determining and fixing your problem!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote:

>HI
>Again back,actualy the problem was in the stored procedure i am using an
>update stmt,i checked by removing the update stmt
>and the hanginh prblm for 2 days not happend hope it's ok and
>thanks for your help.another thing if i want to include the update
>stsmt in the procedure like this What i have to do.
>update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no
>from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT'
>OR #pd_temp.trans_type='PV')
>This stmt is creating prblm,can i give any commit here.Can you please help
>with a sample stmt.
>Thank you
Hi shif,
You have completely lost me now. I just re-read our complete exchange.
First, you posted a stored procedure that creates a temp table #oi,
inserts some data from ap_spled into it, then selects some data from a
join of #oi and ap_spled. You also post an INSERT INTO ap_spled
statement that hangs after this procedure has run.
Now, you suddenly claim that you have removed an update statement that
updates #pd_temp based on data in pay_hd. How is that possible? How can
you remove a statement that never was there?
Anyway - did you already check the db_name and object_name I suggested
in my previous post? Did you inspect if the front-end code leaves any
transactions open? I still believe that to be the cause of your
problems. Fix your frontend to close the transaction after generating
the report and your problems will probably go away.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
I am sorry ,actualy i had copied a differend update syntax but both having
same
effect.
thanks
update #out_inv set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheq_no
from pay_hd
where #out_inv.tr_no = pay_hd.tr_no and
(#out_inv.trans_type='PT' OR #out_inv.trans_type='PV')
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote:
>
> Hi shif,
> You have completely lost me now. I just re-read our complete exchange.
> First, you posted a stored procedure that creates a temp table #oi,
> inserts some data from ap_spled into it, then selects some data from a
> join of #oi and ap_spled. You also post an INSERT INTO ap_spled
> statement that hangs after this procedure has run.
> Now, you suddenly claim that you have removed an update statement that
> updates #pd_temp based on data in pay_hd. How is that possible? How can
> you remove a statement that never was there?
> Anyway - did you already check the db_name and object_name I suggested
> in my previous post? Did you inspect if the front-end code leaves any
> transactions open? I still believe that to be the cause of your
> problems. Fix your frontend to close the transaction after generating
> the report and your problems will probably go away.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||HI
Actualy i have posted is right only thing is that i have changed the table
name instead of #oi i put some other name.
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote:
>
> Hi shif,
> You have completely lost me now. I just re-read our complete exchange.
> First, you posted a stored procedure that creates a temp table #oi,
> inserts some data from ap_spled into it, then selects some data from a
> join of #oi and ap_spled. You also post an INSERT INTO ap_spled
> statement that hangs after this procedure has run.
> Now, you suddenly claim that you have removed an update statement that
> updates #pd_temp based on data in pay_hd. How is that possible? How can
> you remove a statement that never was there?
> Anyway - did you already check the db_name and object_name I suggested
> in my previous post? Did you inspect if the front-end code leaves any
> transactions open? I still believe that to be the cause of your
> problems. Fix your frontend to close the transaction after generating
> the report and your problems will probably go away.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>