azure vm-registration error on oracle openquery after drivers were installed

Question

Post reply

azure vm-registration error on oracle openquery after drivers were installed

stan

SSC Eights!

Points: 968
More actions
September 4, 2024 at 1:08 pm
Go to Answer
#4453963

Hi we installed the oracle 31 and 62 bit oracle drivers on our azure vm under my admin as my peer who is an oracle guy directed. sql 2022 developer is installed there. tnsnames.ora was added. we are looking at the 2nd image and comparing our install steps to what shows there but im not real hopeful because i dont see anything there that explicitly looks like a registration step.
we scripted an oracle linked server create from production and executed it on the vm. it added but after trying to use it and seeing the error below on an openquery and trying to add a linked server from scratch and rebooting we are convinced that sql simply doesnt know that oracle is an available provider on that machine. when trying to add a linked server manually, we dont see anything oracle related in the dropdown of providers.
does anybody have a clue?
frederico_fonseca

SSCoach

Points: 16148
More actions
September 4, 2024 at 1:42 pm
Answer
#4453989

are you sure you ticked the right boxes on the Oracle install? default will not install the required components.
also on a server only the 64bit version should be installed - no reason to have a 32 bit install if SQL is 64 bit - even for workstations now that VS 2022 is 64 bit the 32 bit version can be avoided.
September 5, 2024 at 7:07 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

check this link if it helps.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/linked-servers/set-up-troubleshoot-linked-server

=======================================================================

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 2

Also, MS has a specific client https://www.oracle.com/database/technologies/appdev/ocmt.html that eases installation for powerbi.... may be worth a try

stan SSC Eights! Points: 968 More actions · Answer 3

stay tuned. my peer hasnt really shared a lot about how he got us past the registration error. something about fat client and "lots of tricks".

in the meantime, an identical openquery against oracle that takes less than a second in production was running for 1 hr 22 minutes on the azure vm before i killed it. i am sharing with him everything that is posted here. one challenge we have is that he's an oracle guy , not a sql server guy.

stan SSC Eights! Points: 968 More actions · Answer 4

we arent always the most transparent group but from what i could tell a mismatch of oracle credential info finally got us past the long running open query. specifically it might have been a mismatch amongst what the installer specified when he installed the connectors and what the linked server itself had for credentials and possibly the "owner" in the open query's view name.