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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy