September 16, 2003 at 4:55 am
In .mdb access 2002,
3 tables Tab_A, Tab_B and Tab_C linked to SQL Server
the 3 tables have the same structure with primary key auto number counter 1 by 1
Tab_A has data
the others Tab_B and Tab_C are empty no data
Query_1 to insert all data (*) from Tab_A to Tab_B
Query_2 to insert all data (*) from Tab_A to Tab_C
run Query_1 : (no problem)
run Query_2 : not completed and insert data is totally locked
thanks
ZouhirABID
ZouhirABID
September 16, 2003 at 1:48 pm
Can you reproduce this problem? During the insert, Run sp_who2 to see who is blocking the insert from Tab_A to Tab_C.
September 16, 2003 at 5:41 pm
Hello Allen Cui
Thanks
The result of sp_who2 :
1 BACKGROUND sa . . LAZY WRITER 0 0 09/16 21:32:0 1
2 sleeping sa . . LOG WRITER 0 0 09/16 21:32:0 2
3 BACKGROUND sa . . master SIGNAL HANDLER 0 0 09/16 21:32:0 3
4 BACKGROUND sa . . LOCK MONITOR 0 0 09/16 21:32:0 4
5 BACKGROUND sa . . master TASK MANAGER 0 1 09/16 21:32:0 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 6
7 sleeping sa . . CHECKPOINT SLEEP 0 3 09/16 21:32:0 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 8
9 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 12
13 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 13
14 BACKGROUND sa . . master TASK MANAGER 0 0 09/16 21:32:0 14
51 sleeping CTA2003\Administrateur CTA2003 . adp1SQL AWAITING COMMAND 16 0 09/17 00:11:5 Microsoft Office XP 51
52 RUNNABLE CTA2003\Administrateur CTA2003 . master SELECT INTO 16 0 09/17 00:12:0 SQLDMO_1 52
thanks
ZouhirABID
ZouhirABID
September 16, 2003 at 7:28 pm
I don't see any blocks from the result of sp_who2.
September 17, 2003 at 7:34 am
The problem exists and it is very easy to reproduce
IF and especially IF :
i)the tables have a field counter sequentiel 1 by 1 (automatic number)
ii)when we execute Query_2 immediatly after Query_1
Thanks
ZouhirABID
ZouhirABID
September 17, 2003 at 11:02 am
The result of Profile during running Query_1 and Query_2 :
Line 1 0 17/09/2003 17:05:00
Line 2 14 -- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language Français
set dateformat dmy
set datefirst 1
" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 17/09/2003 17:05:16
Line 3 12 SELECT Config, nValue FROM MSysConf Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:16 3 0 0
Line 4 12 SELECT ""dbo"".""table2"".""c1"" FROM ""dbo"".""table2"" " Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:16 2 0 0
Line 5 12 select substring('NY',status/1024&1+1,1) from master..sysdatabases where name=DB_NAME() Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 16 17/09/2003 17:05:16 28 0 15
Line 6 10 declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT ""c1"",""a1"",""a2"",""a3"" FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 OR ""c1"" = @P2 OR ""c1"" = @P3 OR ""c1"" = @P4 OR ""c1"" = @P5 OR ""c1"" = @P6 OR ""c1"" = @P7 OR ""c1"" = @P8 OR ""c1"" = @P9 OR ""c1"" = @P10', 1, 2, 3, 4, 5, 6, 7, 7, 7, 7
select @P1" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:16 2 0 0
Line 7 12 set implicit_transactions on Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 0 0 0
Line 8 10 declare @P1 int
set @P1=2
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT ""c1"",""a1"",""a2"",""a3"" FROM ""dbo"".""table2"" WHERE ""c1"" = @P1', 1
select @P1" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 9 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 1, '1 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 3 0 0
Line 10 10 exec sp_execute 2, 2 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 11 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 2, '2 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 12 10 exec sp_execute 2, 3 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 13 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 3, '3 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 14 10 exec sp_execute 2, 4 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 15 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 4, '4 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 16 10 exec sp_execute 2, 5 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 17 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 5, '5 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 18 10 exec sp_execute 2, 6 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 19 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 6, '6 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 20 10 exec sp_execute 2, 7 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 21 10 exec sp_executesql N'DELETE FROM ""dbo"".""table2"" WHERE ""c1"" = @P1 AND ""a1"" = @P2 AND ""a2"" IS NULL AND ""a3"" IS NULL', N'@P1 int,@P2 char(50)', 7, '7 '" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:19 2 0 0
Line 22 12 IF @@TRANCOUNT > 0 COMMIT TRAN Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:20 0 0 0
Line 23 12 set implicit_transactions off Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:21 0 0 0
Line 24 10 exec sp_unprepare 2 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:21 0 0 0
Line 25 10 exec sp_unprepare 1 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:21 0 0 0
Line 26 12 SELECT ""dbo"".""table3"".""c1"" FROM ""dbo"".""table3"" " Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 30 17/09/2003 17:05:22 2 0 0
Line 27 12 SELECT ""dbo"".""Table1"".""c1"" FROM ""dbo"".""Table1"" " Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 28 12 set implicit_transactions on Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 0 0 0
Line 29 10 declare @P1 int
set @P1=3
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT ""c1"",""a1"",""a2"",""a3"" FROM ""dbo"".""Table1"" WHERE ""c1"" = @P1', 1
select @P1" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 30 12 SET IDENTITY_INSERT ""dbo"".""table2"" ON " Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 6 0 0
Line 31 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 1, '1 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 32 10 exec sp_execute 3, 2 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 33 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 2, '2 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 34 10 exec sp_execute 3, 3 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 35 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 3, '3 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 36 10 exec sp_execute 3, 4 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 37 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 4, '4 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 38 10 exec sp_execute 3, 5 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 39 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 5, '5 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 40 10 exec sp_execute 3, 6 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 41 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 6, '6 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 42 10 exec sp_execute 3, 7 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 43 10 exec sp_executesql N'INSERT INTO ""dbo"".""table2"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 7, '7 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:28 2 0 0
Line 44 12 IF @@TRANCOUNT > 0 COMMIT TRAN Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:29 0 0 0
Line 45 12 set implicit_transactions off Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:29 0 0 0
Line 46 10 exec sp_unprepare 3 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:29 0 0 0
Line 47 12 SELECT ""dbo"".""Table1"".""c1"" FROM ""dbo"".""Table1"" " Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 48 12 set implicit_transactions on Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 0 0 0
Line 49 10 declare @P1 int
set @P1=4
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT ""c1"",""a1"",""a2"",""a3"" FROM ""dbo"".""Table1"" WHERE ""c1"" = @P1', 1
select @P1" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 50 12 SET IDENTITY_INSERT ""dbo"".""table3"" ON " Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 13 0 0
Line 51 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 1, '1 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 52 10 exec sp_execute 4, 2 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 53 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 2, '2 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 54 10 exec sp_execute 4, 3 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 55 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 3, '3 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 56 10 exec sp_execute 4, 4 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 57 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 4, '4 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 58 10 exec sp_execute 4, 5 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 59 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 5, '5 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 60 10 exec sp_execute 4, 6 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 61 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 6, '6 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 62 10 exec sp_execute 4, 7 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 2 0 0
Line 63 10 exec sp_executesql N'INSERT INTO ""dbo"".""table3"" (""c1"",""a1"",""a2"",""a3"") VALUES (@P1,@P2,@P3,@P4)', N'@P1 int,@P2 char(50),@P3 char(50),@P4 char(50)', 7, '7 ', NULL, NULL" Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:31 4 0 0
Line 64 12 IF @@TRANCOUNT > 0 COMMIT TRAN Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:33 0 0 0
Line 65 12 set implicit_transactions off Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:33 0 0 0
Line 66 10 exec sp_unprepare 4 Administrateur 1824 Microsoft Office XP CTA2003\Administrateur 52 0 17/09/2003 17:05:33 0 0 0
Thansk
ZouhirABID
ZouhirABID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply