I've been using filemaker as an alternate admin interface to our LIMS database which is on SQL Server 2008. It's handy for quick searches and edits, and I'd like to be able to use it to delete certain records in the database rather than using the SQL Server Management Studio. ESS works well for most searching, quick editing, and reporting tasks but for some reason I cannot delete records via this interface. I get no error when I try to do this and it appears from Filemaker that the record is gone. But if I update the found set or search for that record again, either in Filemaker or SSMS, it is still there. I further attempted to use the Execute SQL script step to delete a specific record in the database using syntax like the following, (gleaned from inspection of the Import from ODBC dialog which I successfully used to import records from the same DB)
DELETE FROM "RequestDocuments"
WHERE "RequestDocuments"."RequestDocumentUid" = '99A02BA0-E3D4-4C9E-AFF7-E7092A12F54A'
When I try to run a script that has this SQL in it, I get a dialog that simply says "ODBC Error" with no error code or further explanation.
FYI, the syntax used in SSMS is as follows and my privileges allow me to delete there:
DELETE FROM [Ironwood_LIMS].[dbo].[RequestDocuments]
WHERE RequestDocumentUid = '99A02BA0-E3D4-4C9E-AFF7-E7092A12F54A'
So two questions:
1. Why can't ESS delete records in this table? (wondering if it has something to do with triggers that set off record creation in the audit database)
2. Why can't Execute SQL operate on the database, when Importing from the same DSN works fine? (I've also tried UPDATE queries via Execute SQL with no success, but updating records via ESS works fine)