SQL SERVER IDENTITY COLUMN

[rktbhotla]

hi,

i am using sql server 7.0 when trying a row on a identity column i am getting the following error

Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'TenderDetails' when IDENTITY_INSERT is set to OFF.

How do I make identity_insert to ON?
I tried giving at procedure, but it did not work.
can anyone help me out

[chrisrlong]

Uh, considering the default is OFF and you are actually using a sequence, the question is why do you want to turn it on.

Actually, I have a guess. You are used to Oracle where you always have to insert the value. In SQLServer, you define a column as an IDENTITY column. That means the you never have to insert into that column. SQLServer will not only come up with the next value for you, it will also handle inserting it for you. That column should never be listed in your INSERT statement. However, this also means that you *must* list the columns that you do explicitely want to insert into - no more implicit column list in the insert statement. Of course, it is a very bad practice to use the implicit lists anyway.

ie
INSERT INTO T1
SELECT * FROM T2
...is bad, and not viable when using sequences

INSERT INTO T1
(COL2, COL3)
SELECT
COL2,COL3
FROM T2

...must be used instead.

Hope this makes sense. Let me know if I missed the mark or you have any more questions.

- Chris