MS SQL Server OLAP - ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Author

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Chris Miller

2005-09-29, 8:24 pm

I have a cube that pulls data from an Oracle 9i database.

Using Analysis Manager, I copy and pasted the cube from a production server
to my dev box (so I had a copy of the structure locally). I then edited the
datasource to point at a different Oracle database (and schema/username).
Testing the datasource works fine.

The problem though is that the schema name is different, and the cube definition
contains the schema name as a prefix to almost everything. In the past I
have stopped the OLAP service, opened the cube definition in Access and performed
a search/replace on the schema name. This is a pain (and a hack!), but generally
works without problems. However just now when I tried this, I am getting
the following error when I attempt to process the cube:

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Note that testing the datasource works fine, and I can edit the cube successfully
(if I don't search/replace the schema name, attempting to edit the cube results
in lots of broken links etc, ie the cube is unusable). Switching between
the OraOLEDB.Oracle.1 and MSDAORA.1 providers doesn't help, I just get a
different but similar error (ORA-12154: TNS:could not resolve service name).

So... does anyone either know:

a) What I can do to resolve this error, or
b) How I can see what text the SID/connect descriptor contains so I might
be able to track down what is wrong.

Any help would be greatly appreciated!

Chris

Jéjé

2005-09-30, 3:23 am

verify that the user account used by AS has access to the oracle folder.
if the account cannot read the oracle drivers & TNS config, then you can
receive this type of error.

"Chris Miller" < chris_overseasNOSPAM
@hotmail.com> wrote in message
news:431bbee87a6d88c
793685c3bc7f4@news.microsoft.com...
>I have a cube that pulls data from an Oracle 9i database.
>
> Using Analysis Manager, I copy and pasted the cube from a production
> server to my dev box (so I had a copy of the structure locally). I then
> edited the datasource to point at a different Oracle database (and
> schema/username). Testing the datasource works fine.
>
> The problem though is that the schema name is different, and the cube
> definition contains the schema name as a prefix to almost everything. In
> the past I have stopped the OLAP service, opened the cube definition in
> Access and performed a search/replace on the schema name. This is a pain
> (and a hack!), but generally works without problems. However just now when
> I tried this, I am getting the following error when I attempt to process
> the cube:
>
> ORA-12505: TNS:listener could not resolve SID given in connect descriptor
>
> Note that testing the datasource works fine, and I can edit the cube
> successfully (if I don't search/replace the schema name, attempting to
> edit the cube results in lots of broken links etc, ie the cube is
> unusable). Switching between the OraOLEDB.Oracle.1 and MSDAORA.1 providers
> doesn't help, I just get a different but similar error (ORA-12154:
> TNS:could not resolve service name).
>
>
> So... does anyone either know:
>
> a) What I can do to resolve this error, or
> b) How I can see what text the SID/connect descriptor contains so I might
> be able to track down what is wrong.
>
> Any help would be greatly appreciated!
>
> Chris
>
>

Chris Miller

2005-09-30, 7:23 am

Many thanks, this was indeed the problem - the service was looking for the
TNS config on a network drive. I'm quite baffled as to how the environment
ended up in that state since I haven't changed anything to do with that or
rebooted in over a month, but hey at least I'm back on track again now!

Thanks again.

Chris

> verify that the user account used by AS has access to the oracle
> folder. if the account cannot read the oracle drivers & TNS config,
> then you can receive this type of error.

Sponsored Links