1 2 Previous Next 15 Replies Latest reply on Jun 18, 2012 7:44 PM by BeatriceBeaubien

    XPost Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3

    BeatriceBeaubien

      Hello all,

       

      I am having issues getting an Oracle DSN to work on FMS 11.0v3 on a Windows Server 2008 R2 virtual machine. I am trying to set up ESS to the Oracle database.

       

      Configuration:
      • The Oracle 10g client is installed.

      • The System DSN is created without difficulty using the odbcad32.exe file in the SysWOW64 directory (the Microsoft ODBC for Oracle driver [2010-11-20]). It's pointing to an Oracle 8i database.

      • The configuration of the DSN was set up by an Oracle DBA who has done this before. I was logged in as admin to do this work.

       

      (We also tried with the Oracle driver that comes with 10g. I don't want to muddy the waters, so I have included that issue at the very end of this message.)

       

       

      Problem:
      When I try to use this Microsoft driver DSN to create a table instance in the Relationship Graph, I get the following error:

       

      "ODBC Error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413: Connection not open.
      [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
      [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr)."

       

      I searched for any insight to resolve this, without luck. Any help would be gratefully received.

       

       

      Best wishes,

       

      Beatrice Beaubien, PhD
      i2eye, Toronto, Canada

       

      FileMaker Business Alliance
      FileMaker 11 Certified Developer


      P.S. Additional details:

      • We got the System DSN pointing to an MS SQL database producing a table in the Relationship Graph without any issues.

      • The Oracle 10g 32 bit driver (Oracle in Oracle10Admin_Home1) threw the following error when trying to produce a table in the Relationship Graph:

      "ODBC Error: [Oracle][ODBC][Ora-12154: TNS:could not resolve the connect identifier specified."

      This was after the connection tested successfully in the Oracle ODBC Driver Configuration, and the Oracle DBA was successful in testing the TNS.

      Before it threw this error, it was having difficulty seeing the path to the drivers (it couldn't initialise the SQORA32.dll), so I widened permissions and got the TNS error.

        • 1. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
          greglane

          Hi Beatrice,

           

          Oracle 8i is not specifically supported as a FileMaker 11 ESS data source. The following page lists the supported versions. Toward the bottom of the page you'll find the driver certified for each version:

           

          http://www.filemaker.com/support/technologies/sql.html

           

          Greg

          • 2. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
            BeatriceBeaubien

            Hi Greg,

             

            I forgot about 8i not being on the list. Thank you.

             

            I just spoke to the Oracle DBA and she said that we had also tried a 10g database, and received the same error, however I'm going back to confirm that. I'm also revisiting the Oracle driver install and making sure it's 10.2.0.3 or .4, as Oracle recommends 10.2.0.4 for WIndows Server 2008 R2.

             

            Best wishes,

             

            Beatrice Beaubien, PhD

            i2eye, Toronto, Canada

             

            FileMaker Business Alliance

            FileMaker 11 Certified Developer

            • 3. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
              BeatriceBeaubien

              Hello Greg,

               

              The Oracle DBA installed the correct Oracle driver version and everything is working.

               

              I really appreciate your help.

               

              Best wishes,

               

              Beatrice Beaubien, PhD

              i2eye, Toronto, Canada

               

              FileMaker Business Alliance

              FileMaker 11 Certified Developer

              • 4. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                jdbranum

                Beatrice,

                 

                Which version of Windows 2008 Server was ultimately successful in connecting with your Oracle databases, 32-bit or 64-bit, and what version of the Oracle client and driver?  From your earlier messages, I think it your Windows 2008 Server version may be 64-bit. 

                 

                I have three virtual servers (VMWare) for which I am trying, thusfar unsuccessfully, to get working with Oracle ODBC on both 10g and 11g databases.  The servers are set up with 32-bit version of Windows 2008 Server, and FileMaker Server Advanced 11.0v3.  FileMaker Pro 11 client works perfectly for both ESS and ODBC import with databases run from the desktop, but for FMS11 hosted files, I get error messages similar to those you reported, particularly "[Oracle][ODBC][Ora-12154: TNS:could not resolve the connect identifier specified."  When trying to add an ESS table occurrance to the relationship graph, the list of Oracle tables/views appears without error, but when I select one of them to add, FileMaker Pro reports "ODBC Error".

                 

                I have tried many different combinations of Oracle clients, both full and "InstantClient" versions, from both 10g and 11g installation packages, none working.  The latest version is 11.2.0.1.0.  I get the exact same behavior on each of the three virtual servers.

                 

                Any further information or advice would be appreciated.  I am next going to re-install FileMaker Server Advanced 11 with the v4 update and see if that works.

                 

                Thanks in advance,

                Jame Branum

                Internal FileMaker Developer

                Lawrence Livermore National Laboratory, California

                • 5. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                  Mike_Mitchell

                  Jame -

                   

                  Identical problems here. It works on a hardware box, but on the virtual ... no wanna work.

                   

                  The Oracle people swear there's nothing wrong with the DSNs, but FileMaker won't (can't) connect. Gives me an error that says "Specified driver could not be loaded due to system error 127: The specified procedure could not be found. (Oracle in Desktop10g, C:\Oracle\Desktop10g\BIN\SQORA32.DLL)."

                   

                  Would love to hear any insights. We've been beating our heads against it for weeks.

                   

                  Mike

                  • 6. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                    jdbranum

                    Thanks for the reply, Mike.  What virtualization technology are you using?  Is your Windows Server 2008 the 32-bit or 64-bit version on your VM, and same question for your hardware box?  From what I have tested thus far, main problem appears NOT to be the Oracle piece of the puzzle because FileMaker Pro client, Excel, etc., work fine.  Same result with both full and Instant Oracle client and driver.

                     

                    Our Oracle database system is in the process of being migrated from 10g to 11g RAC, which uses LDAP for database host address resolution (tnsnames.ora no longer is used, and ldap.ora extension is used in the Network\Admin folder instead).  That works just fine, too, with FMP client. The current method using the tnsnames.ora file and Microsoft Driver for Oracle also works with FMP client but not with FM server.

                     

                    James Branum

                    Lawrence Livermore National Laboratory, California

                    • 7. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                      Mike_Mitchell

                      James -

                       

                      We're using VMWare for virtualization, on the R2 version (64 bit) of 2008. Yes, our experience is very similar. Our Oracle jocks test the DSN packages with other software and say they're fine - no connection issues. And I can connect locally using the same DSNs. Try to connect with a hosted database - it blows up.

                       

                      Perhaps VMWare doesn't play nicely with FMS?

                       

                      Mike

                      • 8. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                        jdbranum

                        Mike,

                         

                        We are also using VMWare for virtualization with the 32-bit version of Windows Server 2008 (x86 SP2).  Your answer about using the 62-bit version just burst my bubble hoping such a change would solve this problem.  I will pursue possible VMWare connection with our IT folks who maintain the virtualization and provide updates here with what I find.

                         

                        We also have an older virtual server running Windows 2003 Server, and FMSA 10.  This combo works fine with both the Microsoft and Oracle 10g and 11g clients and drivers ...

                         

                        Thanks,

                        James

                        • 9. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                          Mike_Mitchell

                          James -

                           

                          Identical experience. Older virtual server using 2003 and FMS 10 worked normally.

                           

                          I'm beginning to sense a pattern ...  

                          • 10. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                            LSNOVER

                            1. can you use the tnsping dos program to connect to the server with your Oracle Network Configured Name?

                            2. When you setup the Oracle ODBC driver/DSN windows, can you use the TEST button to successfully connect to Oracle.

                            3. Are you sure you are setting up the DSN in the 32bit version of the ODBC manager, and that it is a system level DSN?

                            • 11. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                              Mike_Mitchell

                              3. Yes, I'm sure it's the 32-bit version, and I'm sure it's system level.

                              2. Yes, the Test Connection button works, and my credentials work as well.

                              1. I have no idea what that is.

                               

                               

                               

                              Mike

                              • 12. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                                jdbranum

                                Lee,

                                 

                                1.  tnsping reports OK.  Also note that FileMaker Pro client, installed on the same virtual server, also works fine for both ODBC Import and ESS.

                                2.  Test Connection button in the ODBC Data Source Administrator also reports success.

                                3.  I am using the 32-bit DS Admin and DSN is system level.

                                 

                                The "only" problem is with FileMaker Server Advanced 11 itself not working.  Error "[Oracle][ODBC][Ora-12154: TNS:could not resolve the connect identifier specified." when trying to open and ESS table previously-configured and working with FMP client then moved to the server.  I get error 801 when executing a client-working ODBC import from the same data source, and "ODBC Error" when trying to complete adding and ESS shadow table to the relationship graph (list of available Oracle views displays, but then errors when trying to select one).

                                 

                                James

                                • 13. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                                  BeatriceBeaubien

                                  Hi James,

                                   

                                  Sorry for the delay.

                                   

                                  We are using the 64 bit version of the OS on both the virtual and hardware box.

                                   

                                  From spending a few hours on both, and a couple of different installations of the 10g client (full and instant), here is a partial list of the issues that had to be resolved:

                                  Instant Client didn't work (may have been permissions issue). The Oracle DBA installed the full version and we confirmed the permissions were properly configured on both boxes and that led to success.

                                  One table was being served in Oracle version 8i, which was a non-starter.

                                  All the caveats about system DSN, 32 bit drivers were well in hand at the beginning of the testing.

                                   

                                  I'll need to look at my notes for more detail and to confirm the virtual box did indeed lead to a successful ESS connection. After reading the exchange between you and Mike, I'm now wondering.

                                   

                                  In the end, what we really needed was the table on Oracle 8i, and even the Oracle 10 solution that was accessible and that supplied this data through a reference (I can get the specific description), did not permit FileMaker to see that table, so we hit another problem that I have been too busy to grapple with. There is a migration strategy to move the critical 8i table to 10g, and I have been waiting to see how that goes.

                                   

                                  Best wishes,

                                   

                                  Beatrice Beaubien, PhD

                                  i2eye, Toronto, Canada

                                   

                                  FileMaker Business Alliance

                                  FileMaker 11 Certified Developer

                                  Knowledge Translation Certified Professional

                                  • 14. Re: [XPost] Problem(s) getting Oracle DSN to work on Windows 2008 R2/FMS 11.0v3
                                    jdbranum

                                    Thanks for the reply, Beatrice.

                                     

                                    Am I correct that your "success" was in being able to perform an ODBC import from the Oracle 10g data source, and adding the 10g data source as an ESS table was NOT successful?  If not, please describe.

                                     

                                    It appears that you are using the 32-bit Oracle driver in your 10g DSN that works.  Is this correct?  My current Windows 2008 Server is set up as 32-bit, so would need to upgrade it to 64-bit if this might lead to at least successful ODBC imports.  The alternative is using a robot FMP client to move data from Oracle.

                                     

                                    Thanks in advance,

                                    James Branum

                                    Internal Developer

                                    Lawrence Livermore National Laboratory, California

                                    1 2 Previous Next