Update Oracle from SQL Server 2000

Question

Post reply

Update Oracle from SQL Server 2000

PaulSp

SSCrazy

Points: 2499
More actions
May 10, 2007 at 4:48 am

#124042

Hi
I have been taking data from our large Oracle server and using it in SQL Server 2000 for some time. Openrowset works fine for Selecting rows.
However, I have now been asked to update rows in the Oracle database from SQL Server.
Does anybody know how I can do this?
I have tried something like:
update u
set u.fieldname = 'TT'
from
openquery(linkedoracleserver,'select * from ORACLETABLE') as u
where u.id = '12345'
It returns the error message:
Invalid object name 'u'.
I feel sure there must be a better way of attempting this UPDATE than using 'openquery' - it takes ages even to run a select command.
Thanks
Paul

Viewing 13 posts - 1 through 12 (of 12 total)

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

Camilo Torres SSCrazy Points: 2034 More actions · Answer 1

Try this to see what happens

update linkedoracleserver..ORACLETABLE

set fieldname = 'TT'

PaulSp SSCrazy Points: 2499 More actions · Answer 2

Hi Camilo

It certainly tried to do something...

However, it returned the error message:

Server: Msg 7317, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' returned an invalid schema definition.

OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid schema definition.].

Any ideas?

Many thanks

Paul

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 3

Try to add the schema to the query:

update linkedoracleserver..SCHEMA.ORACLETABLE

set fieldname = 'TT'

PaulSp SSCrazy Points: 2499 More actions · Answer 4

Hi

Thanks for helping. I really don't know anything much about Oracle, and generally only take data from it. However, for convenience, we would like to do very limited updates.

How might I find out what the SCHEMA is?

Thanks again

Paul

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 5

It's been a while since I've worked w/ Oracle but if I recall the statement below will get the schema name along w/ all the other details...hope this helps!

describe *table name*

-Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

PaulSp SSCrazy Points: 2499 More actions · Answer 6

Thanks for your help, Ben.

However, I'm not sure where to run this from. I don't have access to whatever the Oracle version of QA is.

Also, I tried again what Camilo suggested with another table and it worked fine. So maybe the schema is different with different tables?

Is there another construction that can be used for updating Oracle tables, rather than linked server. I can use OPENROWSET, for example, on all tables.

Thanks

Paul

Camilo Torres SSCrazy Points: 2034 More actions · Answer 7

To find out the "schema", which in this case I think will be the database in ORACLE go to the Object Explorer expand the Linked Servers branch to the Tables branch. the leaves will be the tables in ORACLE you can see. They are prefixed wit the db.

In you statement use LS.DB.Oracle_Table, instead.

We are in the right track if it worked for another table, using your default db.

Keep trying!!!

PaulSp SSCrazy Points: 2499 More actions · Answer 8

Thanks, Camilo

Sorry for the late reply - I've been on vacation.

Yes, I now realise that I do use it with the indicated schema as you suggest. However, for some unremembered reason I use two dots(..) between the name of the linked server and schema. So:

LS..DB.Oracle_Table

Works great on most tables, but not all. And it's slow. I would love to hear of another construction that can update (if there is such a thing) rather than linked server.

Many thanks again

Paul

PorkNBeans Valued Member Points: 59 More actions · Answer 9

You can try DTS:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=96061

PaulSp SSCrazy Points: 2499 More actions · Answer 10

Hi PorkNBeans

Thanks for the link. I've read it through and it still seems that linked server is the best way to go. It's a shame it's so slow.

Thanks again

Paul

Otto Schreibke SSC Eights! Points: 943 More actions · Answer 11

A couple of comments:

Using OpenQuery causes the entire resultset to be shipped across the network.

If it is large, then this will take some time.

For example

Select * from openquery(linkedoracleserver,'select * from ORACLETABLE where id = ''12345''') as u

would return far fewer (one?) records than

Select *

from

openquery(linkedoracleserver,'select * from ORACLETABLE') as u

where u.id = '12345'

which will return all the rows from Oracle to SQL/Server.

In any case I'd prefer to use (LS..DB.OracleTable from your example above)

Select * from LS..DB.OracleTable u where u.id = '12345'

(The syntax for the table source when using a linked server is linked_server.catalog.schema.object.

Oracle only hosts one database (= catalog) per server, hence this paramerer is empty.)

I'd expect

Update LS..DB.OracleTable

Set fieldname = 'TT'

where id = '12345'

to work.

You may have to adjust the security permissions on the oracle server to grant update access to the id you're using in the linked server definition.

Best Regards,

Otto Schreibke

The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)

PaulSp SSCrazy Points: 2499 More actions · Answer 12

Thanks, Otto

I appreciate the openquery tip and will bear that well in mind.

As I say the script

Update LS..DB.OracleTable

Set fieldname = 'TT'

where id = '12345'

works on some tables, but not others. This may be a permissions problem as you suggest and I will contact the 3rd party provider of the Oracle database.

Thanks again

Paul