question

Michael 2 avatar image
Michael 2 asked

Linked server SQL2008 to Oracle

I have this problem :

Migrating from SQLserver2000 32bit to SQLServer2008 x64.

Using a linked server in SQLServer2008 x64 on Win2008 x64. Installed Oracle client etc. x64. Oracle runs on Unix.

Integrationservices is not installed.

My only choice of provider is Oracle OLE DB provider. MSDAORA is not in the list (why not?).

The code runs just fine on the SQL2000 platform. Oracle platform is the same (even the same database) as we are only upgrading to 2008 x64 on the windows/SQL side.

Running the code on SQLServer 2008 generates the error shown below. I have access to tables on the oracle, so permissions should be ok. Any ideas?

Msg 7357, Level 16, State 2, Line 1 Cannot process the object "{Call VUFakturaGrundlagPack.IdQuery('14950254',{resultset 5000,fakturagrundlagid,fakturagrundlagtype,attention,brevattention,invoiceaccount,confirmeddel,pricedate,lastpaymentdate,updsalestype,salesnumber,note,customerref,salestype,userinit,trademarktp,trademarktxt,trademarkclass,lastpaymentdateworkday,udskriv, annulleringsaarsagskode,aarsagsbrginit,aarsagsnavn,masseopkraev,lastchange})}". The OLE DB provider "OraOLEDB.Oracle" for linked server "WSUDV" indicates that either the object has no columns or the current user does not have permissions on that object

Thank You in advance. /Bjarup

openquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Does the machine have the Oracle client installed? If not, I'd get that installed and you should see connectivity issues go away. However, it still sounds like you're hitting a permissions issue. Are you sure about the permissions on the Oracle side, that you used the right login, etc.?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Michael 2 avatar image
Michael 2 answered

As mentioned in the post, Oracle Client is installed and I can access tables, but receive an error when I am calling a package (procedure) on Oracle. The exact same code works just fine on SQLserver 2000.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.