SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 UPDATE/INSERT Problem in Linked Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lathu
Starting Member

India
10 Posts

Posted - 07/28/2011 :  05:01:15  Show Profile  Reply with Quote
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.




Edited by - Lathu on 07/28/2011 05:30:17

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/29/2011 :  02:43:13  Show Profile  Reply with Quote
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

India
10 Posts

Posted - 08/02/2011 :  07:59:58  Show Profile  Reply with Quote
Thank U Visakh.
Its working now..........
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/02/2011 :  12:07:31  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000