Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!
IDENTITY_INSERT
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...
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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 ..
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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?
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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)