The following files are available for download from the Microsoft
Download Center:
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591�
How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Ident.exe includes an algorithm and sample code that
illustrate how to retrieve the identity value from a SQL Server database. You
can use this method for multiple processes or threads doing simultaneous
inserts into the same table. (Note that threads cannot share connections.)
Project description
A master identity table, called tblIdentity, is created. It
consists of the identity, table name, and server process ID number (SPID). An
insert trigger into tblIdentity is created for each table in the database that
satisfies the following criteria:
- Inserts are performed on the table.
- The table has an identity column with values that are
automatically generated by SQL Server.
- The automatically generated identity value is needed
immediately after the insertion.
The following SQL script, located in the Master.sql file,
generates the sample table, insertTbl:
CREATE TABLE insertTbl (myAuto int IDENTITY (1, 1) NOT NULL ,
lname varchar (15) NOT NULL ,
fname varchar (15) NOT NULL )
Because names are not unique, the code uses the identity property of
SQL Server to automatically generate a unique key.
The following SQL
script, located in the Master.sql file, creates an insert trigger for
insertTbl:
CREATE TRIGGER trgAudit ON insertTbl FOR INSERT
AS
INSERT INTO tblIdentity VALUES (@@IDENTITY, 'insertTbl', @@SPID)
Immediately after an insertion into tblIdentity, the trigger fires and
stores:
- the identity SQL Server generated (@@IDENTITY),
- the table name ("insertTbl" in this case), and
- the SPID (the server process ID number of the current
process, guaranteed to be unique for each connection).
After insertion, the program calls a stored procedure (called
sp_getID) to retrieve the identity value.
The code calls the stored
procedure sp_getID (with a negative value for the spid parameter) in the one
time initialization to get the SPID (the server process ID number of the
current process, guaranteed to be unique for each connection). Applications
with multiple threads inserting into the same table must not share a
connection. Each thread must have it's own connection to guarantee a unique
SPID.
NOTE: Applications with multiple threads sharing a connection must use
a more complicated algorithm that includes the thread ID and the SPID to
establish a unique insertion.
After each insertion, the program
calls sp_getID and passes the table name where the insert occurred and the SPID
that was retrieved in the initial call to sp_getID. The stored procedure
sp_getID returns the unique identity value that SQL Server generated on the
insert.
Run the sample
To use the sample application, run the Master.sql file in the SQL
Query Tool from SQL Enterprise Manager. After running the script, you can
verify that the tables and triggers were created successfully by running the
following SQL statement:
insert into insertTbl (lname,fname) VALUES ('Smith','Joe')
select * from tblIdentity
The Results tab should look similar to the following:
iID strTable SPID
----------- --------------- -----------
5 insertTbl 11
(1 row(s) affected)
The iID and SPID values you see will probably not be 5 and 11.
The tblIdentity values verify that the Master.sql script was successful.
Note The sample code uses a sub-optimal method to display the
identity value and is used for illustration purposes only.
The
sample uses the SQLExecDirect function to run the stored procedure and retrieve
the identity value.