I figured I would delve into the Execute SQL script step as I read that this can be quicker.
In what respect? What did you read?
The Execute SQL script step can only be used to insert and update data in an ODBC source. Is that what you are trying to do?
(Want to make sure your expectations are correct).
In the same computer the server is loaded onto, I have setup a System DSN for an SQL database
My issue is that when I try and specify the ODBC Data Source the 'Select ODBC Data Source' window has no entries. If I go to File>Manage>External Data Sources I can see the DSN from the server ok.
I'm assuming you are on Windows.
FMS uses 64-bit DSNs for the ESS feature (to make the live connection and put tables on the graph), but FMP uses 32-bit DSNs for the "Execute SQL" and "Import from ODBC source" script steps. That's why you do not see any DSNs when you work with those script steps.
Find the 32-bit ODBC control panel and add a system DSN there too.
Chapter 9 of the FTS training book describes this well: http://www.filemaker.com/support/training/fts.html
Thanks, I had obviously got the wrong end of the stick. I am not trying to write to the SQL DB, in fact I cannot as I have read only access to it.
I am searching through approximately 27,000 records using a number field (Patient ID). Normally I would index this field, however this does not seem possible with an SQL table, which is why I was looking into using the script step.
I will live with the slight delay for now, it's not a deal breaker at the moment.
Thanks for the prompt response.
You can't index the SQL table from the FileMaker side, but the SQL administrator can create an index on his side.
Don't know if that helps or not.
27000 records in a SQL DB is barely worth indexing. It should be nearly instant with or without.
If you have proper privs on the table, you CAN issue a command through Filemaker to Index fields in most SQL DBs.
The Execute SQL Script Step can be used for retrieving data as well, in a limited way. You can use the Error handling mechanism to return limited values.
You can also call Stored Procedures which in turn can populate tables that you can then make available through the ESS feature, assuming the DB is one of the supported SQL databases.