sql server programming: Linked Server Insertion -- sql server programming

Linked Server Insertion

SKUMARI
10/23/2007 8:43:43 PM

sql server programming:

I have a server A and server B and had a linked server LS from A to B.

I need to insert data into a table t1 that is located in server
B.Table t1 has identity columns. So I am doing the following code

DECLARE @SQL NVARCHAR(500)
SET @SQL=N'set identity_insert t1 on'
exec [LS].B.dbo.sp_executesql @SQL

-- update or insert hard coded values
if not exists( select * from [LS].B.dbo.t1 where ID = 0 )
insert [LS].B.dbo.t1(ID, DESC)
values (0, ' Off' )

I am getting the following error.

Server: Msg 7344, Level 16, State 1, Procedure P1, Line 14
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[LS].[B].[dbo].
[t1]' because of column ID'. The user did not have permission to write
to the column.
[OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No
work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=ID STATUS=DBSTATUS_S_OK], [COLUMN_NAME=DESC
STATUS=DBSTATUS_S_OK]. Data status returned from the provider:
[COLUMN_NAME=ID STATUS=DBSTATUS_E_PERMISSIONDENIED], [COLUMN_NAME=DESC
STATUS=DBSTATUS_E_U...

Appreciate your help on this.

Re: Linked Server Insertion

Uri Dimant
10/24/2007 7:38:54 AM

What if you run

CREATE FUNCTION dbo.fn_get_identity() RETURNS int
AS
BEGIN
RETURN(
SELECT newid
FROM OPENQUERY(
[server_name],
'SET IDENTITY_INSERT tempdb..tbl ON;
INSERT INTO tempdb..tbl (id) VALUES (0)
ROLLBACK;
SELECT SCOPE_IDENTITY() AS newid;') AS O)
END

[quoted text, click to view]