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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 20 Jun 2008 09:57:13 -0700 (PDT)
Message-ID: <4c2ad543-71b6-4537-82fb-09686642a731@d77g2000hsb.googlegroups.com>


On Jun 20, 8:06 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <chris.br..._at_providentinsurance.co.uk> schreef in berichtnews: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- Hide quoted text -
>
> - Show quoted text -

Chris, if you have both database versions 8.1.x and 9.x running on the same server I would just use the 9.x listener to listen for all databases. We have always just used the highest version listener to handle all databases on the server so unless you have a really old database that the newest listener does not work with I would use just one listener, one tnsnames.ora, one listener.ora, and one sqlnet.ora.

HTH -- Mark D Powell -- Received on Fri Jun 20 2008 - 11:57:13 CDT

Original text of this message