Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Angry Unanswered: Cannot connect - Using one method

    Okay I came into this job and they have 1 way of setting up a PC client to connect to an offsite Oracle DB. The problem is this setup is using the Oracle install of the ODBC drivers for Oracle and not the native Microsoft drivers. This Oracle driver has a limitation with the program installed and if its request gets over 50 users it just picks the first one without giving the list of users to pick from. So in other words you search for Smith you get over 50 returns it comes and auto populates with Allan Smith instead of giving you a 50 user list to pick Joan Smith.

    After talking to the app developer they told me the Microsoft ODBC drivers will overcome this limitation and return 50 without auto selecting the first one but I cannot get them to work!

    Our install is as follows Oracle 8i, on an XP Professional machine running MDAC 2.8, it also has Oracle ODBC drivers installed and when these are used in the ODBC settings they work the way they always have (I.E. I have DB connectivity, I can TNSPING, my TNSNAMES.ora file is fine and so fourth.

    When I take out the Oracle ODBC and I put in the Microsoft ODBC (same DSN and Server) I get errors within the application saying no matching names. Mind you this makes me upset because the Microsoft ODBC does not have the little test connection button that the Oracle ODBC button has. I then got a wild hair to open up Microsoft access and just try to link to a single bit of info to test the ODBC and I get a Connection Failed error.

    What I fail to see is why the Microsoft ODBC can't connect to the same thing the Oracle ODBC can with the eact same information in it?

    Does Microsoft ODBC require any special files that maybe the Oracle ODBC did not require in order to conenct? Does the microsoft use something other than the tnsnames.ora?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does the microsoft use something other than the tnsnames.ora?
    What make you conclude that Micro$oft ODBC knows or cares about tnsnames.ora?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2005
    Posts
    5
    Then what does it use to conect out that the Oracle ODBC doesn't use? Is there another file I need to put my connection information into? An ambigous question back to me wasn't really the "help" I was looking for. This nudged me away from thinking Microsoft uses Oracle information to connect but it surely didn't explain anything for me.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    goto control-panel, admin tools, data sources:

    on the User DSN tab you need to add and configure a new DNS a MS ODBC for Oracle connection. Click "Add", then scroll down to "MS ODBC for Oracle". Click "Finish" and then input all of your connection information (this is instead of tnsnames, etc.).

    Now you are set to test in Access to select that ODBC connection you created.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2005
    Posts
    5
    Okay that is what I did in the

    When I take out the Oracle ODBC and I put in the Microsoft ODBC (same DSN and Server) I get errors within the application saying no matching names
    section.

    Let me explain this step by step. I had a working ODBC connection called xxxxdev. This was using Oracles version of ODBC connection it connected to xxxxdev that was in the TSNAMES. Hit test connection it was fine all there.

    Then I took out xxxxdev and rebooted the computer (probably not neceessary but I did it anyway) I then went in to ODBC and added xxxdev DSN using Microsofts Oracle ODBC instead of Oracles.

    Now Microsofts ODBC fields in it - Data source name Decription Username and Server. I put in
    Data Source Name - xxxxdev (just like with Oracles ODBC, this is also what is called for within the app I am using)
    Description - Blank (was also blank using Oracles ODBC)
    User ID - Same user ID supplied by the DB host that we used with the Oracle ODBC.
    Server - I tried xxxxdev (just like it had it in the Oracle ODBC which refferenced the TSNAMES), I tried the XXXX.XXXXX.COM of the company that is listed as the HOST in the TSNAMES.ora.

    My thought is its the Server portion that is failing and if I can just find the right configuration I can get this working.

    Is there a specific way to put the correct information in the Server field? I know the main information I had to put in the tnsnames was the connection type of tcp the host which is the XXXX.XXXXX.COM and then the port of 1522 DO I have to fit all of that into the Server field somewhow?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    do you have the latest MS odbc drivers?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Mar 2005
    Posts
    5
    Well its fixed - yes I had MDAC 2.8 which is the latest according to microsoft. Here is how I have it working .

    I wnt to Microsoft ODBC and put in DSN xxxxdev Description Blank UserID xxxx Server xxxxdev

    Then I went into my TNSNAMES.ora file and I found 2 pointers to xxxxdev I took out the last one so there was only 1 in there formatted correctly and it connected in.

    It seems having the site in the TNSNAMES.ora file twice was what was causing Microsofts ODBC to go screwy (this makes sense because the Oracle ODBC has a dropdown list and you pick the connection server you want so I was not picking the misformatted one).

    oh and btw anacedent I think this concludes to me that Microsoft ODBC for Oracle cares ALOT about the TNSNAMES.ora file.

    Thanks for trying to help guys just glad I was able to get it connected I have also now copied this to 4 other PC's all of them working flawlessly.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    setting up a TNS_ADMIN registry on each client might be helpful for you.
    this basically allows you to have only one MASTER tnsnames.ora file which is located on a network drive which all clients can see.

    then each client does not need a tnsnames.ora file and instead you edit their registry setting to point to the networked tnsnames.ora file.

    then, in the future, when you add/edit tnsnames you only need to edit one file and all clients don't need to edit their own personally.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Mar 2005
    Posts
    5
    You have a pointer link to this process? It would be good to know as we are going to domain based instead of workgroup based for all of our different facilities.

    The process you mention still requires the base oracle client install however right? or is this client not needed if the names file is stored on a server?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •