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 0009/16 21:32:0 1
2 sleeping sa . . LOG WRITER 0009/16 21:32:0 2
3 BACKGROUND sa . . masterSIGNAL HANDLER 0009/16 21:32:0 3
4 BACKGROUND sa . . LOCK MONITOR 0009/16 21:32:0 4
5 BACKGROUND sa . . masterTASK MANAGER 0109/16 21:32:0 5
6 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 6
7 sleeping sa . . CHECKPOINT SLEEP0309/16 21:32:0 7
8 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 8
9 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 9
10 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 10
11 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 11
12 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 12
13 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 13
14 BACKGROUND sa . . masterTASK MANAGER 0009/16 21:32:0 14
51 sleeping CTA2003\AdministrateurCTA2003 . adp1SQLAWAITING COMMAND16009/17 00:11:5Microsoft Office XP51
52 RUNNABLE CTA2003\AdministrateurCTA2003 . masterSELECT INTO 16009/17 00:12:0SQLDMO_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 017/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
"Administrateur1824Microsoft Office XPCTA2003\Administrateur5217/09/2003 17:05:16
Line 3 12 SELECT Config, nValue FROM MSysConfAdministrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:16300
Line 4 12 SELECT ""dbo"".""table2"".""c1"" FROM ""dbo"".""table2"" "Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:16200
Line 5 12 select substring('NY',status/1024&1+1,1) from master..sysdatabases where name=DB_NAME()Administrateur1824Microsoft Office XPCTA2003\Administrateur521617/09/2003 17:05:1628015
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:16200
Line 7 12 set implicit_transactions on Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19000
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19300
Line 10 10 exec sp_execute 2, 2Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
Line 12 10 exec sp_execute 2, 3Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
Line 14 10 exec sp_execute 2, 4Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
Line 16 10 exec sp_execute 2, 5Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
Line 18 10 exec sp_execute 2, 6Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
Line 20 10 exec sp_execute 2, 7Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
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 '"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:19200
Line 22 12 IF @@TRANCOUNT > 0 COMMIT TRANAdministrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:20000
Line 23 12 set implicit_transactions off Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:21000
Line 24 10 exec sp_unprepare 2Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:21000
Line 25 10 exec sp_unprepare 1Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:21000
Line 26 12 SELECT ""dbo"".""table3"".""c1"" FROM ""dbo"".""table3"" "Administrateur1824Microsoft Office XPCTA2003\Administrateur523017/09/2003 17:05:22200
Line 27 12 SELECT ""dbo"".""Table1"".""c1"" FROM ""dbo"".""Table1"" "Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 28 12 set implicit_transactions on Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28000
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 30 12 SET IDENTITY_INSERT ""dbo"".""table2"" ON "Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28600
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 32 10 exec sp_execute 3, 2Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 34 10 exec sp_execute 3, 3Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 36 10 exec sp_execute 3, 4Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 38 10 exec sp_execute 3, 5Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 40 10 exec sp_execute 3, 6Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 42 10 exec sp_execute 3, 7Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:28200
Line 44 12 IF @@TRANCOUNT > 0 COMMIT TRANAdministrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:29000
Line 45 12 set implicit_transactions off Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:29000
Line 46 10 exec sp_unprepare 3Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:29000
Line 47 12 SELECT ""dbo"".""Table1"".""c1"" FROM ""dbo"".""Table1"" "Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
Line 48 12 set implicit_transactions on Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31000
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
Line 50 12 SET IDENTITY_INSERT ""dbo"".""table3"" ON "Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:311300
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 52 10 exec sp_execute 4, 2Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 54 10 exec sp_execute 4, 3Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 56 10 exec sp_execute 4, 4Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 58 10 exec sp_execute 4, 5Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 60 10 exec sp_execute 4, 6Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 62 10 exec sp_execute 4, 7Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31200
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"Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:31400
Line 64 12 IF @@TRANCOUNT > 0 COMMIT TRANAdministrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:33000
Line 65 12 set implicit_transactions off Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:33000
Line 66 10 exec sp_unprepare 4Administrateur1824Microsoft Office XPCTA2003\Administrateur52017/09/2003 17:05:33000
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