You do need a driver, but Microsoft provides a free one - SQL Server Native Client. Configure ODBC DSN same way as on the client and it should be available in Manage/External Data Sources on the client. Once setup it will be available for all.
There is a chapter in FTS Advanced on adding ODBC to the server.
It sounds like you right there, just need a few more details to get it done. The ODBC guide that you are looking for can be found here.
But, here are some quick steps you can take:
* You need to start by creating a DSN on the server. You will configure this DSN to connect to the ODBC source you have, and then the server will be able to use that for scheduled scripts access.
1) Start by opening up the Windows 'Data Sources (ODBC)' application - you probably want the 64-bit version, but it depends on the source that you are connecting to. However, the one that is linked to in the 'Control Panel -> Administrative Tools' is probably the 32-bit version (you can double check by looking at the properties of the shortcut in the Admin Tools panel). You can launch the specific version by going to the specific path:
- The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
- The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
2) Create a new DSN (under System is probably best)
3) Yes, Windows Server should already have the ODBC drivers installed
4) Configure the server connection as suits your needs
* With that done, you need to connect your DB file to this new source
1) In 'File->Manage->External Datasources' click 'New'
2) Give it a name
3) Specify that this is an ODBC source
4) Click the 'Specify' button to see your available DSNs. The one you created above should show up.
5) Click save
Now this ODBC source will show up in your lists of Tables in the Manage Database menu. But you need to create a TO on your graph to be able to specify this new source in your calculations.
Is that it? I probably left a few details out.
Almost everything, in the case where you don't have FMP/FMPA installed on the server, you will need to exclusively use the 64bit DSN and MAKE SURE THE NAME OF THE ODBC CONNECTION IS THE SAME SPELLING as the one you created when testing on the FMP client. If you DO have FMP/FMPA installed on the server, it's best to create both a 32bit and a 64bit DSN, with the same spelling of the DSN name. When FMS uses ESS, it will use the 64bit driver exclusively.
When I developed a solution with this. I installed the Actual ODBC drivers on my development machine, created and tested the solution. ( this was done only to make development easier and not necessary for the final solution )
Then I installed the ODBC driver on the Server with the same names uploaded the files to server and it all worked.
The solution has ODBC connections to mySQL and Postgress Databases.
In filemaker de ODBC are connected via ESS and act almost like normal tables. Had a few issues with lots of calls in a row and caching of data. I save data to shadow records first and then let server loop through these and update the external data (all remote connections). Retrieving data is done directly.
The clients don't need an odbc driver.
Thanks All, This has confirmed what I had hoped. Best of all, the client is going to configure the DSN on the Filemaker Server himself now anyway, woop!