Where is the ODBC DNS set up? On the client computer perhaps? For FMS to do the import, you'll need FMS Advanced which adds the ODBC option for server and all clients connected.
Ann Arbor, MI
Doh! Yes, Gordon, that is it. I couldn't figure out why it didn't work on the server and all the while I forgot that the DNS was set up on a specific desktop machine. Thanks!
By the way, I thought that FMSA ODBC functionality was to enable a hosted FMP file to be an ODBC source, n'est pas?
You're right, Dan. FMSA ODBC allows an FMP file to be an ODBC source. But it also allows any FMP client connected to an FMS hosted database to connect to an external ODBC source.
Regarding your question in French ("n'est pas?"), I don't speak the language. Maybe one of our European colleagues can help with that ;-)
...it also allows any FMP client connected to an FMS hosted database to connect to an external ODBC source.
Great! I didn't realize that. Thanks Gordon.
You don't need FMSA to use the ODBC-related features you've described. FMS (non-advanced) is capable of using the ESS feature and it can also use the Import script step to import data from an ODBC data source via a scheduled script. FMSA is only required if you want to access a hosted FileMaker database via ODBC.
The issue you're experiencing is most likely due to either an authentication issue or a 32 vs 64-bit driver issue. The part of FMS that runs scheduled scripts is still 32-bit and requires a 32-bit driver and DSN when importing from a non-ESS ODBC data source.
Some ODBC data sources (such as the OSAS accounting software) require access to a shared volume. If this is the case, it's likely that the volume is not available to FileMaker Server.
I'd suggest reviewing the FMS logs to see if any errors are being reported in your scheduled script.
I just checked a tech brief on ESS and I believe Greg is correct that you can do this with FMS (or FMP or FMSA). I think the missing piece, Dan, is getting the DSN on the host machine, in this case your FMS box instead of the client. Here's the tech brief:
Thanks Greg. I was unaware of the 32-bit restriction on most ODBC sources. The FMS log does indeed have an 802 error. It is as Gordon suggested - the DSN is on a desktop machine and not the server.
Thanks again, Gordon. Getting the DSN on the server may be more trouble than it is worth as the current database software company installed it on the customer's computer (for proprietory reasons, I suspect). Since this is a temporary issue (the customer is replacing the database on the Linux server with FileMaker), I just changed the process to import when the customer opens the database. We're still in beta so she doesn't do any functionality in it right now. She will simply open the database every couple of days in order to perform the data import.