Hello, Im new too,

Im following a wrox ebook - beginning sql server 2005 express - starter kit edition, and i am having problems with chapter 15 - basically i have a dataset on a form and the stored procedure is meant to return the next integer primary key value automatically to populate the "PersonID" feild in the grid view control. I have run the example file just to make sure i havent made an error copying the programming text but it gives the same result. Basically it allways puts a 0 in the personID column, yet if i execute the stored procedure, the return_value given in the debugging window is correct. Basically the stored procedure looks up the last integer used for this particular table in a seperate table called pkvalue. It takes the table name as an input, as well as another input which is left to default. Im not worried about the stored procedure as when it is run it returns the correct value. I am worried about how i should execute this correctly from a from so that when i add a record it uses the stored procedure correctly to retrieve the return value.

Please please please can someone help. I have tried everything so now i am reduced to begging. I even converted my filesystem to NTFS from Fat32 on a whim. I have used the surface area configuration utility to ensure networking is on and that CLR is on.

Any insight anyone can give, or even a working example file of automatically generating an integer primary key value for a multiuser application? I have had no problems using GUID to set the primary key values, but i want to learn how they can be set with a stored procedure.

Thankyou

I don't know why they even teach it that way... I'd use an IDENTITY column (an autonumbering column type) as the PK column and use Scope_Identity() to return the PK of the record just inserted. Using a separate "sequence" table to do this is a major source of deadlocks and "Death by SQL".

--Jeff Moden

aaaah ok - thankyou

I havent used scope_identity before, it is not covered in this ebook! Would anyone know of any examples i could read up on, or any ebooks i could buy - in which it runs you through the basics of using scope_identity to set the PK feild of new record on a form,

I take it is still run from a stored procedure?

Thankyou

Right after you do an insert to a table that has an IDENTITY column as the PK, you would do a ...

SELECT @SomeVariable = SCOPE_IDENTITY()

@SomeVariable will contain the last value automatically created in the IDENTITY column.

To create a table with an IDENTITY column...

 CREATE TABLE SomeTableName
        (SomePKColumnName INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
         othercolumnnames ....
        )

Anytime you insert into SomeTableName, the SomePKColumnName will automatically increment by 1 for the new row. You cannont insert into the SomePKColumnName... it is an autonumbering column and will take care of it's self.

--Jeff Moden

p.s.

You might want to look up IDENTITY, CREATE TABLE, and SCOPE_IDENTITY in Books Online. If you don't know what that is, it's the "help" system for MS-SQL Server... I don't know if they still call it "Books Online" in SQL Server 2005 but it is generally disquised as "Help, Transact SQL" in SQL Server 2000. Might be able to find it in SQL Sever listings under the Windows Start Button.

--Jeff Moden

ahh Thank you,

This was exatly the help i needed, its a shame they dont teach you that in the book instead they give you that whole seperate pktable approach,

Anyway this is fantastic thanks for your help

Regards

I'm not sure why anyone would teach such a thing... and, they should be publically executed for being so bloody foolish! SQL Server has the IDENTITY property... Oracle has Sequences... why would anyone want to make a PkTable (also called a Sequence or NextID table) which will become a true hotspot and cause more deadlocks than you can shake a stick at? We had a third party vendor that did such a thing to our software... before I got there, they were up to 4000 (not a misprint, it's four THOUSAND) deadlocks per day. I was able to come up with a work around and get down to about 12 deadlocks a week but that's still unsatisfactory in my book and it took a hell of a long time to find all the abhorrent code that used the table. As you can tell by the number of deadlocks, we still haven't repaired all the code.

--Jeff Moden