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/

Go to Top of Page

Lathu
Starting Member

10 Posts

Posted - 2011-08-02 : 07:59:58
Thank U Visakh.
Its working now..........
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -