If you have a ODBC driver installed, create a System DNS on your FMS and have each table that you need be used inside your solution using ESS-technique. Then you do not need to do any SQL-queries and each table behave almost like a ordinary FM table inside your solution.
Our company DBA, Microsoft Engineer, and ODBC vendor engineer finally had a chance to discuss. While the Microsoft engineer helped streamline some things on our server, the ODBC engineer described FMP's handling of ODBC data sources as made for ease of use and not performance. There are some caching issues in play between FWP and the sqlServer when a table occurrence is defined and data is browsed on a layout. His suggestion was to use the SQL import script steps to import the data into FMP instead, then browse as a local table. In my case, the import still took a long time until I turned off the "perform auto-enter calculations and lookups when importing" option. Unchecking this made a massive speed improvement during import. A 30-40 minute import was reduced to 8-10 minutes. I then scripted the calculations after the import was completed. I hope this helps others in the same situation.
Absolutely good advice!
Sent from miPhone
The ODBC engineer suggested a similar approach to Johan's, that is, use FileMaker Server (FMS) to run the SQL query on a schedule, importing the found set into a native FMP table on FMS, then have clients access that. FMP clients can access the FMS table faster than sqlServer and it reduces the number of records vs. the original SQL table. This works in situations where live SQL data is not needed. Unfortunately, I do not have access to a FMS environment and must run the client version directly against the SQL table, so my suggestions are focused there.
you can run the query "locally", but you cannot schedule (middle of the night) like you can with FMServer. so if you do not have access to setting up a Schedule, strongly suggest the idea to whomever does!
Have you considered using JDBC? FileMaker includes the JDBC driver for free in the download for accessing FileMaker itself
For SQL Server, however, you can just as easily get the free MS SQL Server JDBC driver right off the MS website. You can then also do queries, using a JDBC connection, in real-time against SQL Server using tools like "RazorSQL" which works with any JDBC-enabled database (every database).
It's something to test.
I find JDBC with FileMaker is about the same speed as the ExecuteSQL within FileMaker. Not great, but not terrible.
And, if you need to work with data between SQL Server & FileMaker and the ODBC is too slow, try writing (or hiring someone to write) Java code using JDBC (using, in this case, both FileMaker & SQL Server JDBC drivers) to do the data work. Once you switch to JDBC you have full INSERT, APPEND, UPDATE and DELETE.
Java is incredibly fast.
HOPE THIS HELPS.
I'm very interested in trying your approach, but I haven't been able to figure out how to get JDBC setup and configured to get FMP talk to our sqlServer tables. My only background is ODBC so I'm familiar with those config interfaces, but JDBC seems to have a language of it's own. Is there a good resource for config and implementation you'd recommend, other than the documentation on FMP's website? Their JDBC guidance seems targeted at using FMP as the data source, not a sqlServer location.
Wow, after several years of my posting stuff about JDBC, the first taker!!!!
Yes, I'll be glad to help.
First, the "J" in JDBC stands for Java. So, you need to write a (usually small) Java program (TONS of examples online) to interact with SQL Server. To interact with SQL Server, step 1, you download the free JDBC driver from MS and put that in your Java Classpath -- usually in a Java IDE.
The good news is that whether you're using JDBC for SQL Server, Oracle, or for FMP, the basic JDBC code is more or less the same--except possibly for some SQL extensions SQL Server supports like RegExp. But that's the SQL itself, not the code.
Connecting to SQL Server using "Trusted Authentication" is also trivial using JDBC, assuming you have that setup. I can post a sample of what that JDBC connection URL looks like if you need it.
Now, if you also want to interact with FMP, say, to transfer data one way or the other, you would also use the free JDBC driver FMP has. Like with SQL Server, you would put the FMP JDBC driver in your Java Classpath - again, usually in a Java IDE.
As with FMP plug-ins, using JDBC gives you complete CRUD capabilities --INSERTS, UPDATES, QUERIES, and DELETEs (if SQL Server permissions allow these, of course).
The basic idea with JDBC is, in code, you:
1. Create a connection
2. Create a statement (your SQL)
3. (Optionally) Get/Use DB MetaData
4. Get a ResultSet back or run a PreparedStatement (pre-compiled SQL)
5. Do stuff with the ResultSet.
6. Whatever else...
As listed above, JDBC also supports getting DBMetaData which is extremely useful especially for data migration. Using the DB Metadata, you can determine (programmatically in your code) the table names, field names, and field types for both databases. For data migration between SQL Server and FMP, I create a method (procedure/function) in my Java JDBC code to "translate" data types I got from the DBMetaData. FMP knows some types that may be (probably are) different on SQL server.
My longest JDBC program is about 425 lines, but it's not complicated code.
A free Java IDE like Eclipse is probably all you need. Plug-ins for Eclipse (unlike FMP) are free.
Let me know if you have other questions or if you want to post your code, I'll try to help, OK?
HOPE THIS HELPS.