1 Reply Latest reply on May 1, 2010 9:42 AM by MjtBiz

    Cannot see ODBC Data Sources on local MS SQL database



      Cannot see ODBC Data Sources on local MS SQL database


      I am using FileMaker Pro Advanced v11.01 on the Windows platform.  Though I have been using FileMaker for over a decade, it is only in the past several months that I have been using it with MS SQL data sources.  I am having trouble creating an External Data Source, but only when the SQL database is being hosted on the local machine.  Here are the specifics:


      When the SQL database is hosted over the network and I am running FileMaker Pro Advanced 11 in Windows XP, I can create External Data Sources in FileMaker without a problem.  Everything works as expected.  When I move the same MS SQL database to my local machine (Windows 7, 64 bit) and host it there, the ODBC data sources do not show up in FileMaker's Select ODBC Data Sources dialog.  I can, however, use the DSNs that I have set up on this same machine to access the same database via Excel/MS Query, so it appears as if the DSNs are available.


      I have tried using three different drivers (SQL Server, SQL Native Client, and SQL Native Client 10.0), I have set up both User DSNs and System DSNs, and I have pointed the DSN to the named database server as well as the (local) database server.  None of this seems to make a difference.  The only thing that I can think of is that I have a MS SQL hosting preference that is incorrect, or that FileMaker is not compatibel with Windows 7, 64 Bit.


      Any of you SQL gurus have any ideas about what I need to do to fix this one?




      Michael Thompson




        • 1. Re: Cannot see ODBC Data Sources on local MS SQL database

          With the help of Wim Decorte, I have figured this out.  Admittedly, my understanding of the way ODBC works is very limited, but I will make an attempt here to explain it in hope that someone else will find it useful.


          The problem is that there are 64 bit and 32 bit ODBC data sources and FileMaker can only see and use the 32 bit types.  By default the 64 bit versions of Windows 7, Windows Vista, and Windows XP create 64 bit data sources when using the ODBC Data Sources control panel.  So far so good, that makes sense.  It does get a bit confusing from here, however.


          Creating 32 bit data sources in a Windows 64 bit environment, requires the use of the 32 bit version of the ODBC data sources control panel. It is called odbcad32.exe and is located in the SysWOW64 directory.  The 64 bit control panel is also called odbcad32.exe, however, and it is in, you guessed it, the System32 folder.  Huh?!


          So here is where we stand:  There are two types of ODBC data sources: 32 bit and a 64 bit.  There is nothing in the user interface of the Data Sources control panel to inform the user of this.  The separate control panel applications for both 32 and 64 bit drivers have the same name, both ending in 32, and appear no different when in use.  Pouring a fair amount of salt in the wound of confusion, the 64 bit driver is in a folder that ends with 32 and the 32 bit driver is in a folder that ends with 64.  I don't mean to rant, but come on.

          Well, what is a person to do.  When I create a new System DSN in the 32 bit control panel, FM can see it.  Ah, success at last!


          Thanks againg to Wim.  Hope this helps.


          Michael Thompson