Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
phrankbooth
Posting Yak Master
162 Posts |
 Posted - 2007-01-30 : 18:20:28
|
| Hello,In the SP below, I get the following error when I run it:Explicit value must be specified for identity column in table 'Tests' when IDENTITY_INSERT is set to ON.From what I've read of the INSERT method, it says you can add a variable value. So I don;t understand why it won't take mine.CREATE PROC qaspAddTestRecord ASDECLARE @aKey bigintDECLARE @aTestSuiteId bigintexec qaspGetNewID 'Tests', @aKey OUTSELECT @aTestSuiteId= NewID FROM ID WHERE TableName='TestSuite'SET IDENTITY_INSERT Tests ONINSERT INTO Tests (TestSuiteId, TestIDInternal) VALUES (@aTestSuiteId, @aKey)SET IDENTITY_INSERT Tests OFFGO--PhB |
|
|
phrankbooth
Posting Yak Master
162 Posts |
Posted - 2007-01-30 : 18:32:02
|
| Never mind, I answered my own question.You have to tell the SP which field the Key field is, how silly of me.--PhB |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 18:40:13
|
| What is the point of using the Identity option if you aren't going to let SQL Server manage the data for you?Tara Kizer |
|
|
|
phrankbooth
Posting Yak Master
162 Posts |
Posted - 2007-01-31 : 13:03:58
|
| I want to avoid concurrency issues. MS wrote an article saying that if you want to guarantee unique id's, do your own method.What I have now is working quite nicely for me.--PhB |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-31 : 13:09:54
|
| Whether or not it works for you or not doesn't make it the proper design. Identity option is guaranteed to be unique if it is set as the primary key for the table. The constraint guarantees the uniqueness.Tara Kizer |
|
|
|
|
|
|
Explicit Value error, why?