ODBC connection to SQL no longer reliable

[Cable]

I have an ODBC link between FileMaker server and the SQL database back-end for Microsoft Dynamics Great Plains. This link was initially made well over ten years ago and has functioned nearly flawlessly over the years. In that time, both FMP and GP have been on different servers and different version but always the link worked.

Earlier this year, we made three changes. GP moved to a new server. FMP moved to the old GP server. FMS was upgraded from 18 to 19. Every since then, the ODBC connection has been unreliable. It has been failing at least several times a month, often once a week, and sometimes multiple times a week. This is a critical link and must be rock-solid.

One of the things it does is to each night make a copy of some of the data in GP over to FileMaker so that it can be used for various things during the day with much faster speeds than a live data connection. This is done via a scheduled server script and it runs at 2:05am.

One of the things the script does is that it goes to a layout with records from GP and if there are no records showing the script sends out an email alert saying the connection is lost and the script exits. Recently I added a 10 second delay to give the records more time to load, although that was never necessary in the past.

Most nights it works just fine. It used to have a problem once or twice a year but like I said is much more frequent since the upgrade.

Last night was one of those failures.  

When I learned of the problem I remoted from home into the FileMaker server. I launched the client and went to a layout where I could see SQL records. They were present. I logged into the Admin Console and ran the script and it failed. I tried again and it failed again. Usually, rebooting the FileMaker server restores the connection but not this time. I ran it manually from the client and it worked just fine. This is almost always the case. 99% of the time it only fails when run as a scheduled script.

I cannot explain why this is happening, I can’t find anything in the logs to explain it, and I’m at a loss. Unfortunately when this fails it triggers a firestorm of problems. I've reached out to our server consultants and to our FileMaker consultants but I wanted to check here, too, to see if any of you have had a similar issue.

 

[Tim Enloe]

We are having the same issue after upgrading to the latest version of FM Server 19 and moving to a new Mac mini server.  Did you ever get a resolution to this issue?

[Cable]

No, I gave up and went around the problem.

I had a custom SQL view created and used the view instead of the live table. 

[fabriceN]

On 3/16/2022 at 2:27 PM, Cable said:

One of the things it does is to each night make a copy of some of the data in GP over to FileMaker so that it can be used for various things during the day with much faster speeds than a live data connection. This is done via a scheduled server script and it runs at 2:05am.

If I may… knowing that it doesn't address your concern directly, but using ESS for that type of scenario is by far not the best option.
ESS is worth using if you need live data displayed on your layouts or used in scripts, but it's extremely slow and compared to Import records (ODBC source).
No need to have table occurrences or any relationship between FileMaker and the ODBC source. The system DSN is enough.
And that's much faster, with a much cleaner SQL query in most cases, that doesn't make FileMaker look like a dumb monster in the SQL Server log.

Note: should you need to write to the ODBC source (insert/update) , Execute SQL (the script step) has been server compatible since FileMaker 15.

And if you want to make sure that your SQL query doesn't go wrong if you change a field name, you can use this technique.