November 5, 2004 at 4:09 pm
Hi all, I have a question about identity_insert. I can better explain with using naming conventions so I will start with that. I have remote server - Server A and Server B. I have access to remote server through using linked server names - lnk_A, lnk_B. There is a database my_db on local server and Server A and Server B. There is a table my_table in database my_db which has identity column.
I get data from vendor and this is how the content is inserted on my_table local server.
set identity_insert my_db.dbo.my_table on
insert into my_db.dbo.my_table() values()
set identity_insert my_db.dbo.my_table off
However set identity_insert lnk_A.my_db.dbo.my_table on will not work. So I decided to put a stored procedure(my_sp_identity_on and my_sp_identity_off) on Server A and Server B. And call this stored procedure for doing something like this --
begin distributed transaction
execute lnk_A.my_db.dbo.my_sp_identity_on
insert into lnk_A.my_db.dbo.my_table()
select * from my_db.dbo.mytable
execute lnk_A.my_db.dbo.my_sp_identity_off
execute lnk_B.my_db.dbo.my_sp_identity_on
insert into lnk_B.my_db.dbo.my_table()
select * from my_db.dbo.mytable
execute lnk_B.my_db.dbo.my_sp_identity_off
commit transaction
And of course this didn't work either because the stored procedures are out of scope so insert have no knowledge of identity_insert being turned on.
Q.1 Is there any possible solution to above problem?
As a last resort I have thought of putting a new stored procedure - my_sp_insert on server A and server B and allowing those servers to have access to my local server through a linked server lnk_local
create procedure my_sp_insert
as
begin
set identity_insert my_db.dbo.my_table on
insert into my_db.dbo.my_table() values()
select * from lnk_local.my_db.dbo.my_table
set identity_insert my_db.dbo.my_table off
end
And then from my local server I will have something like this
Begin Distributed Transaction
execute lnk_A.my_db.dbo.my_sp_insert
execute lnk_B.my_db.dbo.my_sp_insert
Commit Transaction
But this is actually another question I wanted to ask -- Q.2 Does an error in executing stored procedure (my_sp_insert) on server B will rollback the data on server A?
I hope I am not making my questions too complicated.
November 8, 2004 at 8:00 am
This was removed by the editor as SPAM
November 8, 2004 at 8:19 am
Can you post any more information, such as error messages...?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 8, 2004 at 8:54 am
Do you mean error message for this SQL. Well the only errors reported will be at line 3 (insert...) which would say - the table has identity column and must be set to ON. At line 2 a stored procedure is executed to set identity insert column to ON by invoking a remote procedure but because that stored stored procedure is out of scope with this current session, it has no effect.
begin distributed transaction
execute lnk_A.my_db.dbo.my_sp_identity_on
insert into lnk_A.my_db.dbo.my_table()
select * from my_db.dbo.mytable
execute lnk_A.my_db.dbo.my_sp_identity_off
execute lnk_B.my_db.dbo.my_sp_identity_on
insert into lnk_B.my_db.dbo.my_table()
select * from my_db.dbo.mytable
execute lnk_B.my_db.dbo.my_sp_identity_off
commit transaction
Thanks for your reply. Let me know if you still need more info.
Arpan
November 9, 2004 at 2:15 am
Okay, I think you have a logical error here. It seems that you call the sp which sets the IDENTITY_INSERT to on. And then do the INSERT, but not inside the SP. The SET command is out of scope once the SP has finished, linked server use connection pooling and two queries against a linked server are not guaranteed to use the same connection. Having that said, I think you need to workaround and put the SET along with the INSERT in one procedure otherwise you're pretty much out of luck.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 10, 2004 at 8:02 am
Thanks for your reply. Yes I also thought that this would be the only solution and therefore in my first original post I have a second question which is as follows:
As a last resort I have thought of putting a new stored procedure - my_sp_insert on server A and server B and allowing those servers to have access to my local server through a linked server lnk_local
create procedure my_sp_insert
as
begin
set identity_insert my_db.dbo.my_table on
insert into my_db.dbo.my_table() values()
select * from lnk_local.my_db.dbo.my_table
set identity_insert my_db.dbo.my_table off
end
And then from my local server I will have something like this
Begin Distributed Transaction
execute lnk_A.my_db.dbo.my_sp_insert
execute lnk_B.my_db.dbo.my_sp_insert
Commit Transaction
But this is actually another question I wanted to ask -- Q.2 Does an error in executing stored procedure (my_sp_insert) on server B will rollback the data on server A?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply