If you'll set up ODBC on the server and use the Actual Technologies ODBC driver for FileMaker Server (not the clients), I bet your problems will go away. Using ODBC on client machines means making sure each client ODBC driver is current and working, etc. If you do it on the server and get it right, it will work right for everyone all the time. The downside is that the Actual Technologies ODBC driver for server is $400.
I guess I should have mentioned that my server also has an ActualTechnologies license (250 connection) besides the licenses on my clients.
My understanding is that the script step
IMPORT RECORDS [NO DIALOG ; DSN: PHARMASERV ; CALCULATED SQL TEXT: $SQL ; ADD ; MAC ROMAN]
Does the import locally and not on the server.
Yes, keeping ODBC drivers up to date can be a chore, but I don't see that I have a choice. And I have checked, the suspect macs have up to date drivers...
You can run an import locally on a client connected to the FileMaker Server where the FileMaker Server machine is making the ODBC connection. This is one of the real big benefits of FileMaker server is that it can be the ODBC connection for all of the clients. Is there a reason you would not want the server to make the ODBC connection instead of the local client? It usually solves problems like this. You may have a particular reason, but just checking before delving into what is specifically wrong with those 2 Macs.
I would PREFER that the server make the connection and do the import (heck I paid BIG $$ for server and the ActualTech server level driver). I guess maybe I am missing something basic here because the import script step appears to occur locally, not on the server.
Could you describe the general setup where an IMPORT RECORDS script step request made on a client would be done at the server (outside of using the new FM 13's execute script on server function)? I would love to simplify my setup if possible,
What you described is definitely puzzling. If you can get help from the admin for SQL Server (or if that's you) you may want to try enabling additional query logging on the client and/or server so you can get details (from SQL Server's perspective) about the query that is failing to return the results you're expecting.
Perform Script on Server would be a great option for importing from an ODBC data source using the server's DSN. However, as you said, that requires FileMaker 13 for both client and server, which may not be an immediate option for you.
The only other good option for using the server's DSN would be to use ESS instead of Import from an ODBC data source. Go to a layout based on the ESS table and perform a find based on the criteria in your WHERE clause. Then go to a layout based on your target table and do the import. This is more complicated in your example because your SQL query is joining three tables. You can still make this work, but it will require an approach like one of these:
- Create unstored calc fields in one of your ESS shadow tables to pipe in the related values.
- Do an import (matching) from each of the ESS tables.
- Create a view in SQL server that makes your three joined tables appear as a single, flattened table. Then set up an ESS table occurence for the view and do your find on an associated layout.
The ESS approach is not nearly as efficient as importing using your SQL query so the performance is going to suffer. It can work though.
Hope that helps.
ESS is fine if you want that, as Greg explains, but you don't need it. When you setup a DSN on the server, FileMaker makes that ODBC connection available to all of the clients. You do not need to set up any ODBC connections on the clients. It is all handled on the server. And when you go to import in FileMaker on a client, it will see the DSN's set up as ODBC connections you have set up in your Manage External Data Sources. Just make sure that you have added that ODBC External Data Source and you'll be good to go for either direct imports, exports, or ESS.
ESS works great with MySQL and can often avoid having to do ODBC imports and exports if you use ESS. But ESS can be slow, particularly if if is not a close connection. However, I have often done FileMaker ESS connections to ESS in the same server rack and just one hop away on the switch and it is really fast. Then again, I've done ESS over the WAN to Europe from the US and it really was slow.
Okay, so I removed all traces of ODBC DNS on a client machine as an experiment. When I run the script that contains the Import Records script step, I now get an error. If FM Server is supposed to make that ODBC connection available to all of the clients, it doesn't seem to be working. I have specified the DNS inside Manage External Data Sources (see below).
In my case, ESS is far too slow and using an ODBC import to grab specific data to be manipulated is an order of magnitude faster.
I cannot help think that I am missing something very basic...
Did you remember to do Import via ODBC source instead of Import File?
As far as I know, what you've described is only true for ESS (an external data source of type ODBC). FileMaker Pro (and Go) can not directly use the server's DSNs for the Execute SQL or Import Records (from ODBC data source) script steps.
I'm not sure you were implying otherwise, I just wanted to make it clear that Mike's choices are to either use the (FMS) server's DSN via ESS or define a DSN on each client (or use Perform Script on Server with FileMaker 13).
Greg... apparently you are correct. I was seeing the ODBC source because I was using a development machine that had server and client on it and assumed I was seeing the ODBC source fromt the client through the server.
My apologies, deninger. Greg is correct. You will need to use ESS for it to work the way I was suggesting.... or go back to what you were doing which is managing ODBC connections on each client.
Which gets back to your orginal problem of those 2 Macs not seeing the ODBC source. Let me confirm... you verified they have the latest Actual Drivers. And you said other programs can use the same driver to see data, just not FileMaker. What is the version of MySQL? What versions of FileMaker Pro are you using?
Alternatively, could you try a "Perform on Server" where the server machine is configured properly?
Latest Drivers: Check
Other programs work when FM ODBC does not: Check
The data source is MS SQL Server 2008 R2 (and while I have the "keys" I am not technically the admin as it is for a turnkey solution)
FileMaker Pro 13.x on all machines, including FM 13 Server running on a Mac Mini
Going the route of Perform on Server" does work, but I have avoided this for a variety of reasons, one of which is an error (I cannot find my notes on it right now) that pops up when many different clients call the same script on the server. I have not had time to figure that issue out up to now either.
For now, I am calling the script on the server if the client returns no records by itself. I am still stumped by the issue, though...
Thank you everyone for your help.
Did you try to use the same driver to access the data. Say use Microsoft Excel to use the same Actual Driver on those machines to see the data.
Also, you might compare the update versions of the client programs. There were quite a number of "issues" with the first versions of 13.... I'd make sure they were up to 13.0v4.
Your solution of trying locally and if failing, then on server, is a reasonable compromise. The server error sounds interesting and probably the next thing to work on which you already know.
Wish I could have been more help.