Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 IDENTITY_INSERT is set to OFF

Author  Topic 

sparkadelic
Starting Member

5 Posts

Posted - 2004-12-16 : 16:33:40
I'm trying to insert data into a table but unsuccessful because I get this message -

An SQLException occurred: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot insert explicit value for identity column in table 'EmpNames' when IDENTITY_INSERT is set to OFF.

As you can see I'm using JDBC. Does anybody know how to turn the IDENTITY_INSERT to ON?

Or do you think it's a JDBC problem?





jhermiz

3564 Posts

Posted - 2004-12-16 : 16:41:37
Check that field make sure that the identity is set to yes (you can do this in enterprise manager).
Go to Top of Page

sparkadelic
Starting Member

5 Posts

Posted - 2004-12-16 : 16:43:31
quote:
Originally posted by jhermiz

Check that field make sure that the identity is set to yes (you can do this in enterprise manager).




I checked before I posted the problem but I can't find anything. Can you tell me where to look in Enterprise Manager?
Go to Top of Page

jhermiz

3564 Posts

Posted - 2004-12-16 : 16:46:15
Right click your table goto design view. Click on the row of that field..look at the bottom where the properties are..there is a "IDENTITY" property. Set it to yes.
Go to Top of Page

sparkadelic
Starting Member

5 Posts

Posted - 2004-12-16 : 16:58:09
Well I tried but it's greyed out. What can I do now?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 17:05:51
Well, let's first start with the basic stuff. Do you even want the IDENTITY property turned on? Do you want SQL Server to automatically generate values for your ID column? If you do, then that Identity option in EM must be set to Yes and then you must NOT send values to it on inserts from your application. So you exclude that column in your column list.

Tara
Go to Top of Page

sparkadelic
Starting Member

5 Posts

Posted - 2004-12-16 : 17:12:10
Well yes I want it turned on if I can get rid of the error that's listed. It's complaining about the the IDENTITY_INSERT so I would like to turn it on but it's grayed out.


Also the ID is set to yes but the other column is not.


Go to Top of Page

sparkadelic
Starting Member

5 Posts

Posted - 2004-12-16 : 17:18:06
Ok it's working now. I had to use Integer.parseInt in the java code.
Go to Top of Page

jhermiz

3564 Posts

Posted - 2004-12-16 : 17:19:03
Be careful with parseInt, since the data type doesnt quite correspond with bigints in sql.
What is this fields type ?

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 17:19:23
Identity set to Yes is what you want. Now the problem is with your application. Are you doing your queries through stored procedures or embedded t-sql? Please post the queries. You need to exclude your ID column from the query so that SQL Server handles the data for it. Also, post the name of the ID column unless of course if it's just ID which isn't a very good name if that's what you have chosen, best to use TableName_ID or something like that.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 17:20:17
So what does this Integer.parseInt do? Doesn't sound like it excludes a column.

Tara
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-12-16 : 17:38:27
It appears you have a table with an IDENTITY column and you're trying to INSERT an explicit value into that column. Usually when you have an IDENTITY column you don't insert that value and SQL Server supplies the next value in sequence. You can SELECT back that value by doing a SELECT SCOPE_IDENTITY() right after the INSERT.

If you truly do want to INSERT an explicit value into an IDENTITY column do it like this.

SET IDENTITY_INSERT EmpNames ON

... INSERT statement here ...

SET IDENTITY_INSERT EmpNames OFF

After you've INSERTed an explicit value into an identity column you'll probably need to correct the see as docemented here: http://www.sqlteam.com/item.asp?ItemID=8003.

I usually only insert explicit values into an identity when I'm importing data. If you're doing during your regular processing I'd reconsider whether you really want an identity column.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page
   

- Advertisement -