Inserting rows into remote server with identity column

Question

Inserting rows into remote server with identity column

ladyblue1075

SSC Eights!

Points: 861
More actions
April 11, 2012 at 4:23 am

#267158

Hi,
I'm trying to insert rows into a remote server with identity column. Here's my sample script:
SET IDENTITY_INSERT [remoteserver].db1.dbo.TWMS_Permission ON
INSERT INTO [remoteserver].db1.dbo.TWMS_Permission (
[PermissionID],[AppId],[Description],[WarningMsg],[ExceptionMsg],[DisableWarning])
SELECT
[PermissionID],[AppId],[Description],[WarningMsg],[ExceptionMsg],[DisableWarning]
FROM db2.dbo.TWMS_Permission
SET IDENTITY_INSERT [remoteserver].db1.dbo.TWMS_Permission OFF
This returns the error below:
OLE DB provider "SQLNCLI10" for linked server "172.16.20.1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "172.16.20.1" could not INSERT INTO table "[172.16.20.1].[kellis_dev].[dbo].[TWMS_Application]" because of column "AppId". The user did not have permission to write to the column.
I don't have any access on the remote so I can edit the identity column.
Please help if what else I can do to make it work.
Thanks!
April 11, 2012 at 4:52 am

This was removed by the editor as SPAM
April 11, 2012 at 6:37 am

This was removed by the editor as SPAM
April 11, 2012 at 7:20 am

This was removed by the editor as SPAM
April 11, 2012 at 7:54 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ladyblue1075 SSC Eights! Points: 861 More actions · Answer 1

I can't log on to the remoteserver. It was only the name that has been provided. I can only log to my local server. So what should I do now?

ladyblue1075 SSC Eights! Points: 861 More actions · Answer 2

Stewart "Arturius" Campbell (4/11/2012)
Provided the user allocated for the remote server has the necessary priviliges, consider effecting the following on your local server:
EXEC ('SET IDENTITY_INSERT db1.dbo.TWMS_Permission ON') AT [RemoteServer]
INSERT INTO [remoteserver].db1.dbo.TWMS_Permission (
[PermissionID],[AppId],[Description],[WarningMsg],[ExceptionMsg],[DisableWarning])
SELECT
[PermissionID],[AppId],[Description],[WarningMsg],[ExceptionMsg],[DisableWarning]
FROM db2.dbo.TWMS_Permission
EXEC ('SET IDENTITY_INSERT db1.dbo.TWMS_Permission OFF') AT [RemoteServer]

This gives me this error:

Msg 7411, Level 16, State 1, Line 1

Server '172.16.20.1' is not configured for RPC.

ladyblue1075 SSC Eights! Points: 861 More actions · Answer 3

Stewart "Arturius" Campbell (4/11/2012)
This means that the Server Options page of the linked server (on your local server) needs to be changed;
the RPC and RPC Out options must be set to true.

Sorry, Im not familiar with this. How to get to this window?

ladyblue1075 SSC Eights! Points: 861 More actions · Answer 4

Stewart "Arturius" Campbell (4/11/2012)
ladyblue1075 (4/11/2012)
Sorry, Im not familiar with this. How to get to this window?
Within SQL Server Management Studio, connect to the local server.
Then:
> Expand the "Server Objects" folder
> Within this folder, expand the "Linked Servers" folder
> Whithin this folder, locate the remote server name. Right-click on the name and select Properties.
> This will open the page displayed above. Select the "Server Options" page.
Please Refer: Linked Server Properties (General Page)
and: Linked Server Properties (Server Options Page)

There are no linked server on the list, only Providers.

But I can saw "SQLNCLI10" which was being mentioned on link server error:

[font="Courier New"]OLE DB provider "SQLNCLI10" for linked server "remoteserver" returned message "Deferred prepare could not be completed.".[/font]

ladyblue1075 SSC Eights! Points: 861 More actions · Answer 5

I'm done with this!

What I did is removed the ID from the insert statement and it automatically generated the ID.

It inserts without problem now!

Thanks for all who participated.