UPDATE/INSERT Problem in Linked Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UPDATE/INSERT Problem in Linked Server

Author

Topic

Lathu
Starting Member

10 Posts

Posted - 2011-07-28 : 05:01:15

I Have a Linked Server "RSNEWTEST" for the purpose of sending data from Oracle Tables to SqlServer Tables and Vise Versa.

I am Getting the Success result when i tried to push data from Oracle to Sqlserver.

But, when i am trying to Delete data In Oracle Table from SqlServer, i am getting error, Like........

OLE DB provider 'OraOLEDB.Oracle' could not delete from table 'SELECT * FROM CustCabs'. There was a recoverable, provider-specific error, such as an RPC failure.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

Here My Linked Server Details.

sp_addlinkedserver @server = 'RSNEWTEST',@srvproduct = 'MyServer',@provider = 'OraOLEDB.Oracle', @datasrc='MyServer',@provstr = 'DATABASE=MyServer;Data Source=MyServer;User ID=challi;Password=challi;'

sp_addlinkedsrvlogin 'RSNEWTEST', false, NULL, 'challi', 'challi'

exec sp_serveroption RSNEWTEST, 'collation compatible', 'true'
exec sp_serveroption RSNEWTEST, 'rpc out', 'true'
exec sp_serveroption RSNEWTEST, 'use remote collation', 'true'

--Below insert query is working when i want to Put data from oracle to SqlServer Table.
insert into CustCabs select * from openquery(RSNEWTEST,'select * from CustCabs')
SELECT * FROM openquery(RSNEWTEST,'select * from CustCabs')

--Below Delete query is giving error when i want to Delete data From Oracle Table.

DELETE OPENQUERY (RSNEWTEST, 'SELECT * from CustCabs')

Also Help for the INSERT Data to Oracle Table from the SqlServer Table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 02:43:13

try setting rpc option also to true

exec sp_serveroption RSNEWTEST, 'rpc', 'true'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lathu
Starting Member

10 Posts

Posted - 2011-08-02 : 07:59:58

Thank U Visakh.
Its working now..........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 12:07:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources