x

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

more ▼

asked Feb 03 '10 at 08:28 AM in Default

Michael 2 gravatar image

Michael 2
22 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.?

more ▼

answered Feb 03 '10 at 10:29 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 05 '10 at 04:44 AM

Michael 2 gravatar image

Michael 2
22 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x26

asked: Feb 03 '10 at 08:28 AM

Seen: 2145 times

Last Updated: Feb 03 '10 at 08:28 AM