September 28, 2003 at 11:11 am
In database .mdb access 2002,
3 tables Tab_A, Tab_B and Tab_C linked to SQL Server 2000
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 : fail (no ligne inserted)
I believe that the problem is :
SET IDENTITY_INSERT "dbo"."table2" ON
is not followed by
SET IDENTITY_INSERT "dbo"."table2" OFF
The result of Profile during running Query_1 and Query_2 :
Audit Login -- 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
Microsoft Office XP Administrateur CTA2003\Administrateur 832 52 2003-09-20 10:18:27.827
SQL:BatchCompleted select substring('NY',status/1024&1+1,1) from master..sysdatabases where name=DB_NAME() Microsoft Office XP Administrateur CTA2003\Administrateur 0 28 0 160 832 52 2003-09-20 10:18:27.840
SQL:BatchCompleted SELECT Config, nValue FROM MSysConf Microsoft Office XP Administrateur CTA2003\Administrateur 0 3 0 0 832 52 2003-09-20 10:18:28.000
SQL:BatchCompleted SELECT "dbo"."Table1"."c1" FROM "dbo"."Table1" Microsoft Office XP Administrateur CTA2003\Administrateur 0 24 0 0 832 52 2003-09-20 10:18:28.030
SQL:BatchCompleted set implicit_transactions on Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 0 0 832 52 2003-09-20 10:18:28.043
RPC:Completed declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT "c1","a1","a2","a3" FROM "dbo"."Table1" WHERE "c1" = @P1', 1
select @P1 Microsoft Office XP Administrateur CTA2003\Administrateur 0 43 0 33 832 52 2003-09-20 10:18:28.043
SQL:BatchCompleted SET IDENTITY_INSERT "dbo"."table2" ON Microsoft Office XP Administrateur CTA2003\Administrateur 0 6 0 0 832 52 2003-09-20 10:18:28.090
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 46 1 63 832 52 2003-09-20 10:18:28.090
RPC:Completed exec sp_execute 1, 2 Microsoft Office XP Administrateur CTA2003\Administrateur 0 11 0 16 832 52 2003-09-20 10:18:28.153
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed exec sp_execute 1, 3 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed exec sp_execute 1, 4 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed exec sp_execute 1, 5 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed exec sp_execute 1, 6 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed exec sp_execute 1, 7 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:28.187
SQL:BatchCompleted IF @@TRANCOUNT > 0 COMMIT TRAN Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 1 0 832 52 2003-09-20 10:18:29.030
SQL:BatchCompleted set implicit_transactions off Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 0 0 832 52 2003-09-20 10:18:29.030
RPC:Completed exec sp_unprepare 1 Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 0 0 832 52 2003-09-20 10:18:29.030
SQL:BatchCompleted SELECT "dbo"."Table1"."c1" FROM "dbo"."Table1" Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.373
SQL:BatchCompleted set implicit_transactions on Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 0 0 832 52 2003-09-20 10:18:31.373
RPC:Completed declare @P1 int
set @P1=2
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT "c1","a1","a2","a3" FROM "dbo"."Table1" WHERE "c1" = @P1', 1
select @P1 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.373
SQL:BatchCompleted SET IDENTITY_INSERT "dbo"."table3" ON Microsoft Office XP Administrateur CTA2003\Administrateur 0 13 0 93 832 52 2003-09-20 10:18:31.373
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 44 0 16 832 52 2003-09-20 10:18:31.483
RPC:Completed exec sp_execute 2, 2 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.500
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 4 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed exec sp_execute 2, 3 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 4 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed exec sp_execute 2, 4 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 4 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed exec sp_execute 2, 5 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 4 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed exec sp_execute 2, 6 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 4 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed exec sp_execute 2, 7 Microsoft Office XP Administrateur CTA2003\Administrateur 0 2 0 0 832 52 2003-09-20 10:18:31.513
RPC:Completed 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 Microsoft Office XP Administrateur CTA2003\Administrateur 0 4 0 0 832 52 2003-09-20 10:18:31.513
SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRAN Microsoft Office XP Administrateur CTA2003\Administrateur 15 0 0 13 832 52 2003-09-20 10:18:34.780
SQL:BatchCompleted set implicit_transactions off Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 0 0 832 52 2003-09-20 10:18:34.793
RPC:Completed exec sp_unprepare 2 Microsoft Office XP Administrateur CTA2003\Administrateur 0 0 0 0 832 52 2003-09-20 10:18:34.793
ZouhirABID
ZouhirABID
September 30, 2003 at 2:08 am
Is that a case of far to much information...
Your problem is that you can only have the identity insert on for one table at a time so you would need:
SET IDENTITY_INSERT Table_B ON
Run query_1
SET IDENTITY_INSERT Table_B OFF
SET IDENTITY_INSERT Table_C ON
Run query_2
SET IDENTITY_INSERT Table_C OFF
September 30, 2003 at 10:16 am
Thank you
Yes, absolutely we need to have
SET IDENTITY_INSERT Table_B ON
Run query_1
SET IDENTITY_INSERT Table_B OFF
SET IDENTITY_INSERT Table_C ON
Run query_2
SET IDENTITY_INSERT Table_C OFF
But, Run query_1 is in .mdb access database,
exactly we open the query and all the actions are automatically done by SQL Server or OCDB … implicit transactions
But these actions are incomplete.
ZouhirABID
ZouhirABID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply