January 17, 2002 at 5:35 pm
How do I keep the @@identity the same when I move a database from one server to another?
Right now it always returns a NULL value.
I am moving from SQL server 7.0 to another machine running the same version , SQL server 7.0.
January 18, 2002 at 5:13 am
If you detach/reattach there will be no change in the current identity value. @@Identity is only going to return a value if you have just inserted a record.
Andy
January 18, 2002 at 8:03 am
Andy,
When we move (detach) the database to from the old NT Server to the new (attach), the @@identity on the new server is NULL. The old data is all there, but the value, you would think, should retain the last value, so that when new records are added to the database (now on the new server), they would continue from where the value last left off. As it is, the value is NULL.
January 18, 2002 at 8:21 am
@@Identity doesn't work this way. @@Identity is tied to a particular statement. It's not tied to a database.
For instance, if I do a
INSERT MyTable
(Column1, Column2)
VALUES
('For Column1', 'For Column2')
For a table defined as:
CREATE TABLE MyTable (
MyID int IDENTITY,
Column1 char(20),
Column2 char(20)
)
and I then do a
SELECT @@IDENTITY
I should get the value just inserted into that identity column. If someone else inserts after me, I still get the Identity value generated by *my* statement.
Now, going a step further, it returns the last identity value generated by my statement. This may or may not be the identity value inserted into the table. For instance, If I have the following trigger declared:
CREATE TRIGGER trig_i_MyTable
ON MyTable
FOR INSERT
AS
INSERT MyTable_Audit
(Column1, Column2)
SELECT Column1, Column2
FROM inserted
and my Audit table is defined as:
CREATE TABLE MyTable_Audit (
MyID int IDENTITY,
Column1 char(20),
Column2 char(20)
)
and if for whatever reason they are out of sync, the @@IDENTITY is going to return the identity value generated from the insert due to the INSERT trigger.
Hopefully this clarifies @@IDENTITY.
If you want to know the current seed value, look at using DBCC CHECKIDENT. Steve has written an article on reseeding using this command:
http://www.sqlservercentral.com/columnists/sjones/identityreset.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 18, 2002 at 8:24 am
Has another insert been run subsequent to the insert onto the original table (having an identity column)? If there has, and the new insert affects a table not having an identity column, the value of @@identity will be null. If there hasn't, then I guess this means that the identity values are stored in the one of the system databases.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 18, 2002 at 8:36 am
Sorry Brian - just got to read your comments.
I suppose then that the value of @@identity is really dependant on a individual's OLEDB connection and what statements have been issued on that connection. So for Syakist by detaching the database the connection was broken and consequently the value of @@identity lost. For your trigger example, the trigger fires in the same implicit transaction as the insert, so can be though of as another statement issued on the same connection.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 18, 2002 at 9:30 am
Right, but the original insert caused the second insert to fire because of the trigger. So SQL Server is going to return the identity from the trigger insert. Hence the addition of SCOPE_IDENTITY() and IDENT_CURRENT() in SQL 2K.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply