I have a customer that wants to me update a SQL server from FileMaker. Normally I just do an export to CSV or excel and they do an import but this customer would like to connect directly to the SQL server and update it.
This is something I can do with FileMaker correct? If so can is there a good starting point for me to learn how to do this?
I have worked with SQL server and queries long ago for a EQ emu server of all things and well I hated it 
But it did teach me a little so I am not starting from scratch but it still feels like it.
I have not learned yet exactly what I am connecting too but the FileMaker setup is FileMaker 19 with a local server setup. Both servers will be on the same network.
You can use FileMaker with the ESS feature
See
Accessing External SQL Data Sources (ESS) Overview and Troubleshooting
Or if you like to use MBS FileMaker Plugin, check the SQL functions.
They allow you to connect via script, run some SQL commands and move data.
Either record by record yourself in a loop or with powerful batch functions like FM.SQL.InsertRecordsToSQL or SQL.InsertRecords and similar ones.
2 Likes
Thank you for the quick response. I will start reading.
We’ve a number of systems connected via ESS to either MySQL or Microsoft SQL server. However, those connected to Microsoft SQL server are usually there to carry out the reverse and migrate the data from the SQL server to a new solution built in FileMaker.
The actual ODBC and ESS setup is pretty easy. However, we’ve experienced situations where only selected tables appear within FileMaker and, to date haven’t managed to get the support for the legacy systems to identify why this is. We assume it is a setup or permissions issue, but with out limited SQL knowledge, haven’t managed to identify this.
As we are usually pulling data from SQL, we normally make up for the missing tables by using SSMS, which overcomes the problem.
We’ve little or no experience of the reverse direction you’re planning or whether you intend to do the data migration within SQL or use FileMaker to write to the SQL tables, but I hope the above is of some interest.
Regards
Andy
1 Like
Thanks for your insight. I am a little worried about how well it will work but at the same time I am excited to have a reason to test it. I have considered it might be better for them to pull the data than it would be for me to push it. I do plan on bringing that up to them.
Right now I know so little it is hard to give good advice.
For anyone looking into ESS, I like to recommend the following read:
In this case, it might not be quite as helpful, as the above doc mostly pertains to pulling data out of the SQL source. But, it still might have some value in that it goes into some under-the-hood details which might help to get into the ESS mindset.
3 Likes
Found another source of information I found useful from the 2017 Dev Con.
4 Likes
