Solved

IDENTITY_INSERT

Posted on 2004-08-09
16
1,580 Views
Last Modified: 2008-02-01
Greetings...can someone please help me resolve this problem (I'm new to SQL):

I have a table (RotaryDrilling) with an identity field (Record_Number) that uses

CREATE Trigger insert_key ON RotaryDrilling
FOR Insert AS
INSERT INTO RotaryDrillingCA (Record_Number) SELECT Record_Number FROM inserted
Order by Record_Number

to update Record_Number to a second table (RotaryDrillingCA). Using an Access (2000) form to input a new record, I get the following error:

ODBC--insert on a linked table 'dbo_RotaryDrilling' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'RotaryDrilling' when IDENTITY_INSERT is set to ON. (#545)

I don't want to specify an explicit value...what do I need to do? This is very urgent.

Thanks...
0
Comment
Question by:rsanglim
  • 9
  • 3
  • 2
  • +1
16 Comments
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
Most likely you have set IDENTITY_INSERT somewhere else in your code

If you want the identity field to be populated auotmatically, you'll have to issue a

SET IDENTITY_INSERT RotaryDrilling  OFF

statement
(I think the problem is not related to the trigegr itself)
0
 

Author Comment

by:rsanglim
Comment Utility
I agree that the problem is not related to the trigger itself...I was trying to supply all the information that may be relevant. I'm not 'savvy' enough to have set IDENTITY_INSERT somewhere else...how do I set it to OFF?
0
 

Author Comment

by:rsanglim
Comment Utility
e.g., where would I issue the OFF statement?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
Query Analyzer.
0
 

Author Comment

by:rsanglim
Comment Utility
Using the Query Analyzer, can I execute code that issues the OFF statement each time a user wants to enter a new record via the form? Is that another INSERT trigger (or an addition to what I currently have)? My apologies, but this is all foreign to me.

0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
No, you should not have to set it to off whenever you need to issue an insert statement.

If you don't remember setting it to on, maybe you could search your code to find where it was set to on.

Basically this should be done only in rare occasions, since automatic incremental number is what identity fields are intended for.

Maybe yuo had to set it ON for a special loading process ??
0
 
LVL 5

Accepted Solution

by:
hkamal earned 500 total points
Comment Utility
You can switch off IDENTITY_INSERT at db level;
SET IDENTITY_INSERT OFF
in your session or add it prior to the INSERT statement in your trigger ..

0
 

Author Comment

by:rsanglim
Comment Utility
The only code I have is an INSERT/DELETE trigger(s). When I designed the table, I identified the field as PK, Identity=Yes, Seed = 1, Increment = 1. It occurs to me that this may be the source of my problem when I attempt to insert a new record.

The problem doesn't arise when inserting into the table but, rather, when I attempt to insert the record number into the foreign key of the associated table (Identity = No).
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:rsanglim
Comment Utility
hkamal - I was just experimenting with your suggestion (add prior to the INSERT statement of my trigger) prior to my last post, and seem to be having some initial success.
0
 

Author Comment

by:rsanglim
Comment Utility
Adding SET IDENTITY_INSERT OFF prior to the INSERT statement of the trigger has not resolved the problem...
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
Okay, this

"Explicit value must be specified for identity column in table 'RotaryDrilling' when IDENTITY_INSERT is set to ON. (#545)"

That the trigger is not the problem. Please show how you are inserting (stored procedure?).
0
 
LVL 5

Expert Comment

by:hkamal
Comment Utility
rsanglim, what happens when you do a manual insert :
INSERT INTO RotaryDrillingCA (Record_Number) SELECT 123456 (from QA)

And when you do it inside a proc:
create proc InsertTest as
  INSERT INTO RotaryDrillingCA (Record_Number) SELECT 123456 (from QA)
go
exec InsertTest
0
 

Author Comment

by:rsanglim
Comment Utility
I'm sorry, hkamal - what is your reference to ""(from QA)"?

Also, I just ran a test on a PDA that pushes data into the 'master' table (RotaryDrilling). The data went in fine, and the associated Record_Number was inserted into the FK of the other table...no issues.
0
 

Author Comment

by:rsanglim
Comment Utility
hkamal - I'm fairly convinced your original solution re: << add it prior to the INSERT statement in your trigger >> is the answer. However, even though I've included the statement in the INSERT trigger it seems as though it needs to be 'refreshed'. I ran the SET IDENTITY_INSERT OFF in the Query Analyzer, on the off chance it would work, and it did.

On one hand, that's good news. On the other, I shouldn't have to refresh every time a new record is inserted should I?
0
 

Author Comment

by:rsanglim
Comment Utility
hkamal - thanks...I think this is going to get it.
0
 
LVL 5

Expert Comment

by:hkamal
Comment Utility
OK good stuff !
I always assume people use Query Analyser and would know the shorthand "QA".
I asked you to test the manual parts as I was curious which part caused the problem (though adding SET IDENTITY_INSERT OFF  right before the insert would fix it anyway
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now