Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
MS SQL: Key column information is insufficient... (Read 7166 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 10
MS SQL: Key column information is insufficient...
06.09.07 at 15:22:53
 
MS SQL: Key column information is insufficient...
 
While updating a test table in SQL Server Express 2005 I received this error in SQL*XL:
Key column information is insufficient or incorrect. Too many rows were affected by update.
 
A short investigation into this problem.
 
Database: SQL Server Express 2005
Driver: Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
 
I created this table: create table test (a numeric, b date);
I created some test data executing 15 times: insert into test (1, null);
 
Using SQL*XL in Excel I queried the table: select * from mytable.
I changed some values in column a and tried to run the multiple rows update. It errors with the message:
Key column information is insufficient or incorrect. Too many rows were affected by update.
 
I tried to do the same thing in Microsoft SQL Server Management Studio Express. Changing values in column a the tool errors:
The data in row 8 was not committed.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (15 rows).

 
It seems it hits the same underlying problem. I bet this has to do with the absence of a key column. In Microsoft SQL Server Management Studio Express I set a primary key on column a (after emptying the table). Now I can change values in the columns. However when I by accident use a value that has already been used I get an error. Logical since there is a primary key on the field.
 
Trying views:
creating a view on this table with create view vw as select * from test rendered to an updatable view. Through the multiple rows update feature in SQL*XL data can be modified in both columns.
 
Now a more complex scenario. I created a new table with the same columns as test but without the primary key. I called it test2. Now I create a view on these tables joining them by column a:
create view vw2 as select test.a a, test.b b1, test2.b b2 from test, test2 where test.a = test2.a
 
Trying to do an update on the results of select * from vw2 failed with this message:
Insufficient key column information for updating or refreshing.
 
Actually it fails when I try to update column b2 (this column is based on test2.b). When I update b1 (based on test.b) or a (based on test.a) I do not get any errors. It seems it is related to table test2 not having a primary key.  
 
Adding a primary key to table test2 using server manager actually allows me to update all columns! However... inspecting the underlying tables test and test2 it seems only column a in table test has been changed! Running select * from vw2 indeed shows that the just updated row is not in the view anymore. Condition test.a = test2.a does not hold anymore. This is not very good although I can understand why it happens.
Back to top
 
« Last Edit: 06.09.07 at 15:46:17 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1