Jonathan Monroe

Internally, FM stores the database name along with the table and column names. This so that you can include two tables with the same name from two different databases. The implication of this is that you can change the server (by changing the IP address in your DSN), but the new server must have the same database name as the old server. If the database names are different (i.e. one is named "staging" and the other is named "production"), you will see exactly the problem you are describing. If it's not feasible for you to go through and re-link your layouts manually, you might try contacting one of the FM tool providers out there that externally manipulate the FM schema (Dave Simpson at www.fmpromigrator.com, for example). It might be possible to perform a schema-wide "search and replace" on your database name. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

How are you interacting with the SQL Server database - are you accessing SQL tables that have been added to your FM Relationships graph using ESS? Or are you executing an "Import Records" script step? If you are using ESS, then it is important to keep in mind that FM caches around 50 SQL records locally to help with performance. To make sure you have the latest records, you should execute a "Perform Find" that will cause the cache to be refreshed. If you are using the "Import Records" script step, then you should probably take a look at your SQL statement to make sure it matches the criteria that would include your most current records. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

It is true that FileMaker "External SQL Sources" (ESS) does not currently support SQLite. You will get a "database not supported" error if you try to add a SQLite database to your FM Relationships graph. However you can still read and write data from FM to SQLite (and other databases) using the "Import Records" and "Execute SQL" script steps. You just have to construct the correct SQL statements (SELECT / INSERT / UPDATE / DELETE) to be executed by SQLite. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

I believe it is not possible to insert into a MS SQL Server table that has a trigger defined. This is an artifact of the mechanism FM uses to replicate FM behavior on a SQL table. I don't know that this limitation is documented anywhere, but I've seen this question posted a few times for both Mac and Windows platforms. The problem does not occur with MySQL or Oracle databases. I suggest that you file a bug report on the FM bug report forum: http://forums.filemaker.com/hives/1eea103f05/summary Provide as much information as you can: version of Windows, version of SQL Server, table definition, etc. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

The FileMaker SQL engine does not support the concept of foreign key constrains. The notion of a relationship on the FM "Relationships" graph is not necessarily the same as the notion of a relationship in SQL. There is no way to create a FM-style relationship using SQL (or any libraries that manipulate SQL, such as ADO). There might be a way to make an external application create a FM-style relationship, using the techniques employed by FMPro Migrator (for example). I don't know what those techniques are, but I'm pretty sure there are FM experts out there who might be able to provide you with a solution. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

The optimal way of interoperating with SQL Server is to use "External SQL Sources" (ESS), which it sounds like you have working. With ESS, you don't have to write an SQL at all. However, there may be times when you want to execute SQL statements directly - for example, if you need to execute a stored procedure. In that case, there are two FM script steps which support ODBC: "Execute SQL" and "Import Records". "Execute SQL" is for SQL statements that don't return a recordset, such as INSERT and UPDATE statements. "Import Records" is for executing SQL statements that return recordset, such as the SELECT statement. Either one can be used for executing stored procedures. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

The FM 11 ODBC driver is not compatible with FM 10. The FM 10 ODBC driver (SequeLink) is not compatible with FM 11. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

You don't need the FileMaker ODBC drivers in order to use ESS. FMS connects through SQL Server through Microsoft's SQL Native Client. Since FMS is a 32-bit process, you need to configure your SQL Server DSN using the 32-bit ODBC Administrator (and the 32-bit Native Client). Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

Internally, FM stores the database name along with the table and column names. This so that you can include two tables with the same name from two different databases. The implication of this is that you can change the server (by changing the IP address in your DSN), but the new server must have the same database name as the old server. If the database names are different (i.e. one is named "staging" and the other is named "production"), you will see exactly the problem you are describing. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

Did you install the 11.0.3.309 Server update, too (and reboot)? I believe that both updates are required. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

The recent "11v3" update addresses xDBC memory leaks: Driver: http://help.filemaker.com/app/answers/detail/a_id/9363 Server: http://help.filemaker.com/app/answers/detail/a_id/9283 I believe you will need to install them both (the server and the driver updates). See FileMaker documentation for details. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

The FileMaker ODBC driver can be downloaded here: http://help.filemaker.com/app/answers/detail/a_id/7865 When using FMP as a data source, the ODBC client application must be on the same machine as FMP. In your scenario, you would either need to run FMP on your Windows PC or run FMSA on your Mac. Jonathan Monroe Actual Technologies - ODBC for Mac OS X http://www.actualtech.com

For the hanging issue, I suggest you set the "Network Timeout" value in the Advanced Options panel of your DSN to a small value, such as 10 seconds. If FM leaves your MySQL connection open, it may eventually timeout by the MySQL server or some piece of network equipment, unbeknownst to the driver. The next time the driver attempts to connect on the closed connection, it will wait for 3x the timeout period. For the default setting, that will be up to 10 minutes. Setting it to 10 seconds will cause the "hang" to be no longer than 30 seconds. For the crash, I'm not aware of any driver-related issue that might be causing that, as long as you are using a version of MySQL that is officially supported by FileMaker (i.e. MySQL 5 or later). The next time you see the crash, please send us the crash report along with the version of the driver you're using to support@actualtech.com. Jonathan Monroe Actual Technologies - ODBC for Mac OS X www.actualtech.com

The fundamental concept to keep in mind with FM's "External SQL Sources" (ESS) feature is: It lets you work with a SQL table as if it were a FM table. That means you can make a layout based on the SQL table, and you can reference the SQL table in your FM scripts. ESS takes care of creating and executing the SQL statements needed to make the SQL table act like a FM table. You have no visibility or control over the SQL statements it generates. ESS only uses ODBC drivers for connecting to the SQL database, and only supports 3 DBMS: MySQL, Oracle and MS SQL Server. It is supported by both FMP and FM Server. A nice benefit is that, when used with FMS, the ODBC driver only needs to be installed on the server. FMS handles all the ODBC connections on behalf of the FMP clients. Jonathan Monroe Actual Technologies - ODBC for Mac OS X www.actualtech.com

It sounds like you are reading the documentation for FM 11, but you are using the CD for FM 10 (or earlier). Is that the case? The instructions changed for FM 11, so if you need to use the FM 10 drivers, you should use the installation documentation on the FM 10 CD. Jonathan Monroe Actual Technologies - ODBC for Mac OS X www.actualtech.com