May 3, 2002 at 2:18 am
I'm trying to use the OraOLEDB provider instead of the standard MSDAORA provider as it seems to support distributed transactions.
(SQL 2000 SP1 connection to Oracle 8.1)
Has anyone managed to get this working?
Steven
May 3, 2002 at 4:22 am
Yes, but it was a pain. What exactly are you running into and what do you need to be able to do when you have implemented?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 3, 2002 at 4:54 am
Firstly i'm creating a linked server using @provider = 'OraOLEDB.Oracle'.
Then when I try and use it I get the following error using QA to read a table 'OLE DB provider 'OraOLEDB.Oracle' reported an error. Access denied.'
When I create the linked server using the MS provider it works fine.
What I would like is to fire off a trigger from a sql server table to insert/update an oracle table, then in the case of the insert return the identity. (This is currently at the 'proof of concept' stage). The other option may be merge replication, i've got transactional working fine with no problem , but I get an 'no output error' when I try and use merge. Haven't spent much time on that yet. Trying to get this working first.
Note:
I did get this working on my local pc yesterday (sql 2000 developer edition) but sql went down with an exception error when I did a select using the linked server, and now get a User32.dll error whenever i try to use that linked server). A rebuild may on my local pc may be in order.
Steven
May 3, 2002 at 7:48 am
Simply done this way on my server. First I installed the Oracle client with the OLE driver. Setup a namedinstance in NET8 so my server will know how to find. Test Named instance login with SQL*PLUS on the server entering Username, Password and Host String (i use the name). Then once I know the connection can be made from the box I open SQL EM and drill Server->Management->Security. From there I right click and do New Linked Server... which pops up a dialog box which there i do the following.
On the General Tab
Set Name
Select Other data source and from drop pick "Oracle Provider for OLE DB"
Enter product name as ORACLE
Enter Data source as name I put in in NET8
On the Security Tab
Usually I use "Be made using this security context" and enter Login Name and Password but some I have mapped to impersonate.
I do nothing on the Server Options tab.
I then normally reboot the server to make sure everything in memory is clean.
Test when done.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 3, 2002 at 7:52 am
I'll try that
thanks for your help
Steven
May 3, 2002 at 8:21 am
No luck, still get access denied.
I was doing it that way except I manually editing the tnsnames.ora and sqlnet.ora files, whereas you mentioned Net8 (I had hoped this was a different way of setting up the alias)
I am going to try and find out why i get access denied message using that provider, when the same account gives me access using the ms provider. Hopefully thats the key
Steven
May 3, 2002 at 8:44 am
I remember when we moved from Oracle 7 to Oracle 8i I got this problem and am looking for what fixed it. I beleive I just reinstalled the client software again. Have you tried that? As well if you do try can you list the items you have installed and I can compare with what I have? Also did you try the SQL*Plus client to see if you coudl connect from that server with th Oracle drive?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply