September 29, 2003 at 10:56 am
In a 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 9:20 am
A couple of questions: Are you inserting data into the primary keys? SQL Server won't allow you insert data into the auto number field.
Also, how are you linking the Access table?
Richard
September 30, 2003 at 10:38 am
Thank you.
Yes, I am inserting data into the primary keys, and SQL Server allows insert data into the auto number field the first time we open (execute) Query_1. The second Query_2 is blocked.
The .mdb Access database is used as a frontal; tables are linked (attached) to a SQL Server Database.
The queries Query_1 and Query_2 are in the .md Access database.
ZouhirABID
ZouhirABID
September 30, 2003 at 12:35 pm
I was able to reproduce your error. I am looking into it. Do you need to have the autonumbers on the second and third tables? What are you using the tables for? Are they foreign keys into the first table?
Richard
October 1, 2003 at 3:19 am
Thank you
Yes, I need to have the autonumbers on the second and third tables. It must be a key in each table. For the application, management and accounting, from time to time, that the data needs to be removed and stored into the same structure, and in the other hand, sometimes that the data needs to be restored. The autonumber is used in others tables (3 or 4) for relation [1 1] or [1 n] between tables.
ZouhirABID
ZouhirABID
October 1, 2003 at 10:03 am
Zouhir,
If all you are doing is saving the data for archiving or for performance sake, I would take off the auto number on the subsequent tables because the inital table creates the key. All you need to do is make sure that the subsequent tables have an integer as the key field. This would help ensure that your data doesn't accidentaly get changed if you don't update the key field and the autonumber field does it for you.
Hope this helps,
Richard
October 1, 2003 at 1:27 pm
Mr Richard
Thank you,
Yes, to be away the problem ... But, I need also to change the auto number and the only way is to insert data with the new value for the auto number.
To resolve the problem, I thing it is necessary to have a way to correct the implicit transaction séquence ...
ZouhirABID
ZouhirABID
October 1, 2003 at 4:14 pm
Zouhir,
I do not believe the implicit transactions are what are causing the problem. The problem is with the SET IDENTITY_INSERT "dbo"."B" ON statements. The SET IDENTITY_INSERT on allows you to insert values into an identity column. If you look at the log file Access is setting it on but not off. I found this in Books Online:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
Access is using the same session to get to the data, but it is not turning the IDENTITY_INSERT off after it is done. Access knows it got an error, but doesn't report it correctly. I do not know how to force Access to send the SET IDENTITY_INSERT off.
As to getting around this, you may have to write a stored procedure to do the multiple copies.
Richard
Edited by - rbinnington on 10/01/2003 4:17:24 PM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply