SET IDENTITY_INSERT NOR WORKING

Question

SET IDENTITY_INSERT NOR WORKING

Dugi

SSCoach

Points: 17998
More actions
June 4, 2008 at 8:07 am

#124128

Hello, I have little problem here with IDENTITY COLUMN in my table!?
The situation is:
ID INT IDENTITY(1,1) NOT NULL,
FNAME VARCHAR(30),
LNAME VARCHAR(30)
Ok, I truncate it and I want to insert data from another table with the same structure, but the data is not coming to may table even I set the IDENTITY_INSERT OFF, it works just if I go to the design of that table and setting the Is Identity = NO, then run T-SQL statement for inserting and it works fine!
So what happened with SET IDENTITY_INSERT tablename OFF - NOT WORKING! WHY WHY WHY!!!!
:w00t:
============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You need to do SET IDENTITY_INSERT ON in order to explicitly insert a value into the identity column.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Dugi SSCoach Points: 17998 More actions · Answer 2

I try ON and OFF but the same results ... the original error msg is :

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'TT' can only be specified when a column list is used and IDENTITY_INSERT is ON.

so confused why it happened! :w00t::w00t:

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 3

HAAAAAA I fixed it !!! now works OK OK OK

the problem was NOT FOR REPLICATION .... :hehe::hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

The error message is pretty clear. You have to specify a column list and turn IDENTITY_INSERT on. Please post the INSERT statement.

Greg

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Congratulations. Check out the links in Jack's signature block about what info you need to provide to get good help in forums.

Greg

Dugi SSCoach Points: 17998 More actions · Answer 6

I didn't use it so often couz I worked allways with DBCC checkident and allways working with latest data...so I need it today and doesn't works and it makes me confused what happening!

But I find it!

thnx anyway

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

srinivasa_varadhan SSC Rookie Points: 47 More actions · Answer 7

I am also getting the same error

•Explicit value must be specified for identity column in table 'WebBid' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)

I have chagened the Not For Replication to No

The replication works but if the same table is been affected by a trigger then the replication is not happening

Can anyon help me.....