Identity column gives me error msg SQL 2000

[hsingh1981]

Hi all,

I have just migrated all my access tables to sql 2000. However when insert a new record it doesn't auto increment my record in the id field.

So i went in design view of the T_Equipment table and Set the identity to yes and this technically should auto increment a record. The identity seed is 1 and Identity Increment is 1.

However i get an error msg when i save the changes? The error msg is:

'R_Equipment' table
- Unable to modify table.  
Invalid transaction state: [Microsoft][ODBC SQL Server Driver][SQL Server]The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

 

[Qik3Coder]

You may want to make sure that your starting seed is higher than the maximum value you have in the table.

I am assuming that is a spelling error with the T_equipment and R_equipment.

-Sometimes the answer to your question is the hack that works

 

[bborissov]

That error message has nothing common with Identity field.
Somewhere in your code you have COMMIT TRANSACTION w/o starting one.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Qik3Coder]

Boris, that's what i was thinking. Just wanted to make sure that he didn't have any other weird issues with the identity column trying to overlap.

-Sometimes the answer to your question is the hack that works

 

[hsingh1981]

Thankyou for the replies.

Its not the code when i actually go to the table in design view and saw that Identity was set to NO. I changed that to yes and the identity seed and identity increment was 1. When i save the table it comes up with the above error.

But thankyou for your suggestions....anyother recommendations?

yes it was slight spelling error R_Equipment

 

[macleod1021]

Check to make sure you don't have any duplicate entries in the column you're trying to make an identity field.

im in ur stakz, overflowin ur heapz!

 

[Qik3Coder]

In case your trying to figure out how to check for dupes (wow this going to be fun when you are checking the identity col...)

Select t1.*
from table1 t1
JOIN table1 t2 ON
t1.col1 = t2.col1
t1.col2 = t2.col2
...etc

or you could do this:

Select t1.ID, count(t1.id) from table1 t1
group by t1.ID
Having count(t1.id) > 2





-Sometimes the answer to your question is the hack that works

 

[hsingh1981]

I imported the tables from a ms access 2003 database. The ID field used to be an auto number and be a primary key.

I run the sql statement to check whether it has any duplicates or not and it doesn't contain any duplicates.


If i create a new table and set an id to increment by setting identity to yes it works. however if i set the identity to no.... and add numbers in the id field and change back the identity to yes it comes with the same error msg. (i made sure theres no duplicates)

thankyou for your replies again....any other ideas or is this some sort of bug in my sql 2000?