Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Course Of The Month
4 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
IDENTITY_INSERT
Posted on 2004-08-09
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...
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...
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
-
3
2- +1
16 Comments
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)
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)
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?
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.
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 ??
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 ??
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 ..
SET IDENTITY_INSERT OFF
in your session or add it prior to the INSERT statement in your trigger ..
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).
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).
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.
Adding SET IDENTITY_INSERT OFF prior to the INSERT statement of the trigger has not resolved the problem...
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?).
"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?).
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
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
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.
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.
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?
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?
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses
Course of the Month4 days, 6 hours left to enroll
689 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.