SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 IDENTITY_INSERT is set to OFF
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

sparkadelic
Starting Member

5 Posts

Posted - 12/16/2004 :  16:33:40  Show Profile
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?






Edited by - sparkadelic on 12/16/2004 16:41:23

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/16/2004 :  16:41:37  Show Profile  Visit jhermiz's Homepage
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 - 12/16/2004 :  16:43:31  Show Profile
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
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/16/2004 :  16:46:15  Show Profile  Visit jhermiz's Homepage
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 - 12/16/2004 :  16:58:09  Show Profile
Well I tried but it's greyed out. What can I do now?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 12/16/2004 :  17:05:51  Show Profile  Visit tkizer's Homepage
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 - 12/16/2004 :  17:12:10  Show Profile
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.



Edited by - sparkadelic on 12/16/2004 17:14:31
Go to Top of Page

sparkadelic
Starting Member

5 Posts

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

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/16/2004 :  17:19:03  Show Profile  Visit jhermiz's Homepage
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

USA
37157 Posts

Posted - 12/16/2004 :  17:19:23  Show Profile  Visit tkizer's Homepage
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

USA
37157 Posts

Posted - 12/16/2004 :  17:20:17  Show Profile  Visit tkizer's Homepage
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

USA
4137 Posts

Posted - 12/16/2004 :  17:38:27  Show Profile  Visit graz's Homepage
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000