Re: Heteregenous connections client ==> oracle database ==> sqlserver

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 20 Jun 2008 14:06:05 +0200
Message-ID: <485b9d2e$0$14352$e4fe514c@news.xs4all.nl>

<chris.brown_at_providentinsurance.co.uk> schreef in bericht news:2a44625e-546f-453a-9387-9e0935d09052_at_25g2000hsx.googlegroups.com... On Jun 19, 6:27 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Mark D Powell" <Mark.Pow..._at_eds.com> schreef in
> berichtnews:2cf07768-94d6-456c-b4a8-50970d25ec4e_at_f24g2000prh.googlegroups.com...
> On Jun 19, 8:28 am, chris.br..._at_providentinsurance.co.uk wrote:
>
>
>
>
>
> > I am using 9i client to connect a 9i database 9.2.0.5 enterprise / 9i
> > listener
>
> > I have setup an ODBC connection to a sqlserver database on this server
>
> > I can run the following code on the server
>
> > select * from mi_v_agents_at_rdt ...where rdt is a link
>
> > whenever I try and run from my client it fails / ORA-12154
>
> > I am a bit confused as I can use the listener to connect to 9i
> > oracle / but obviously not to the sqlserver database via the link .. I
> > am thinking it is related to tnsnames files being used but I would
> > have thought that I would be just using config on the server once I'd
> > connected to the 9i database.
>
> > From the documentation I've put what I thought were the correct
> > entries in tnsnames.ora/listener.ora
> > and in \hs\admin\initfile on the server
>
> > Any suggestions on methods of diagnozing\tracing where I am failing
> > would be appreciated
>
> > cheers
> > Chris B
>
> What are the details for the database link: public vs private, current
> user or explicit user?
>
> Only the tnsnames.ora file on the database server needs to have an
> entry for the remote database. The listener.ora and hs_init.ora would
> also only need to exist on the database server.
>
> The question Shakespeare asked about the username in use and the
> request for information on the contents of the tnsnames.ora,
> listener.ora is also important.
>
> HTH -- Mark D Powell --
>
> ================================================
> You're right, only server settings should matter. I just asked for both
> files to be sure to get the right one, and to check for typo's.
> Doesn't the default domain setting on the client have any influence? I
> thought that when you use a non-fully qualified link name, the domain was
> resolved on the client by the names.default_domain = setting in
> SQLNET.ORA.
> But I'm not too sure about that in cobination with db-links, because in
> that
> case the server is the client.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Thanks for all postings / I have managed to get this to work by playing around with sqlnet.ora on the server

Just to ensure that I walk away from this wth the right lesson learned ..could you advise on the following :-

If a log onto a v9 database ( from a remote client ) is it the v9 home tnsnames & sqlnet files that matter on the server when accessing a linked server
...I have confused myself by using a 9 listener and an 8 listener at different times

cheers
Chris B

client ===============> v 9 database

ora8 listener

execute select * from mi_v_agents_at_rdt;

So once I've logged onto 9 database and then try and access linked server ...which tnsnames and sqlnet files come into play on the server the 9 ones or the 8 ones



To avoid this problem, you can set the TNSADMIN environment parameter at the server.

Shakespeare Received on Fri Jun 20 2008 - 07:06:05 CDT

Original text of this message