Wednesday, May 16, 2007

Linked servers and an Identity property

Someone in sqlserver.programming group asked nice question about getting an IDENTITY from the linked server. I came up with the following solution. I assume that there is a table on the linked server that has one column defined as indentity property.

CREATE FUNCTION dbo.fn_get_remoteidentity()
RETURNS INT
AS
BEGIN
RETURN(
SELECT row_id
FROM OPENQUERY(
[put here your server name],
'SET NOCOUNT ON;
INSERT INTO dbname..usertable DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS row_id;') AS Der)
END
Another question was how to insert an INDENTITY property in the table on remote(linked) server?
Well in that case you will need to build dynamic SQL along with SET IDENTITY_INSERT
command.

DECLARE @sql NVARCHAR(400)
SELECT @sql = '
CREATE TABLE test(id INT IDENTITY(1,1))
SET IDENTITY_INSERT test ON
INSERT test DEFAULT VALUES
SET IDENTITY_INSERT test OFF
SELECT id FROM test'
--Usage
EXEC [LinkedServer].master.dbo.sp_executesql @sql

3 comments:

haiyan said...

Two years passed, I still remembered the scene of fighting against wolves in Tir Chonaill with friends, sharing fruit which was got by our mabinogi gold around a fire, fishing in a clean pond. Players will have five seconds freezed to make their own preparation, when the fight beings, win or lose, it depends on your own cheap mabinogi. Here is some mabinogi money I took months ago; I was the guy wielding a thick sword. And I know it is rude enough to beat a woman. But, we can use some mabinogi online gold to edit some dates in the setup menu to change the clothes we wear. As a kind of fantastic online game, I think Mabinogi is more similar to comic and need to buy mabinogi gold.

Replica Watches said...

29047126483369175 I play dofus Replica Watches for one year, I Replica Rolex Watches want to get some Replica Watch kamas to buy Replica Chanel Watches item for my character. So, I search "Replica Swiss Watches" on google and found many website. As Exact Replica Graham Watch the tips from the forum, I just review the Swiss Replica Watches websites and choose some Replica Montblanc Watches quality sites to Replica Cartier Watches compare the price, and go to their Replica Breguet Watches online support to make Replica Breitling Watches the test. And Last Chaos Gold I decide to use Replica BRM Watch at the end. And Tag Heuer Replica Watch that is the Replica IWC Watch beginning..

aiya said...
This comment has been removed by a blog administrator.