We are trying to create an updatable query between SQL server (version 2016) and FileMaker server 16 database using ODBC (all systems are 64 bit).
We are able to get data (SELECT) from the filemaker database using either Linked servers (via the openquery function) or the openrowset function.
SELECT * FROM OPENQUERY(DSN_Name, 'Select * from Test')
OPENROWSET('MSDASQL','DSN=DSN_Name;UID=User;PWD=password', 'SELECT * FROM Test')
Both methods return all the rows from table “test” just fine.
Problems arise when we try to update a record in the “test” table. We tried with:
SELECT * FROM OPENQUERY(DSN_Name, 'Update Test SET Valore = 5 Where ID = 2')
UPDATE Tabella SET Tabella.Valore = 5 FROM OPENQUERY(DSN_Name, 'Select * from Test') Tabella WHERE Tabella.ID = 2
SELECT * FROM OPENROWSET('MSDASQL', 'DSN=DSN_Name;UID=User;PWD=password', 'Update Test SET Valore = 7 Where ID = 2')
We always get the same error message:
“Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.”
If we try the same exact query using the same DSN in Microsoft Access (using a passthrough query) the update completes successfully, so we are inclined to think that the filemaker server configuration is correct.
Thank you for your help