IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.
You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[table
name] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call tha
t
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP
--

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
--
David Portas
SQL Server MVP
--

Identity Field

Folks

I am inserting some values into a table with the following stmt

Insert into table(number,name) values ('12','name')

In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
of the ID (the most recent one) in the next select statement.

ie Select @.@.identity from table (any condition??)

How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.

Thanks for the help,There is only one @.@.identity tracked for any given connection to MS-SQL (each spid). To retrieve its value, you just select it (no table needed). Something like:DECLARE @.id INT
INSERT INTO HHGtable (theAnswer) VALUES (43) // whatever
SELECT @.id = @.@.identity-PatP|||Pat

Thanks for the idea. BTW I have a question can I use

select Max(ID) from table

So that It gives only the maximum value and it would be same value
when the record is inserted? I am just asking your suggestion. Is that logically correct??

Thanks for the help,|||I would think so, but only as long as the tables next identity never is reset to a lower value (dbcc checkident is able to do so).