FM importing outdated SQL data

[lsmall]

I am hoping that by some chance someone can recognize this behavior pattern as I have run out of troubleshooting ideas. I apologize in advance if its too vague.

I am running FM 11v3 on OS X 10.6.7. I use Actualtechnologies ActualSQLserver to interface with my organization's microsoft SQL server. I just updated it to the latest version as part of my troubleshooting (3.0.12 I think). Microsoft SQL server version 09.00.4035

I have a rather complicated data collection script that runs nightly between 2am and 5am. Most of the data is collected from other sources, but some is collected from the SQL server. I have found that the fields that get their data from the SQL server import outdated data. The fields on the SQL side update frequently and can have different values on different days, but I seem to always import only the first value that ever populated the SQL field in that record. It could have been updated multiple times over weeks and I still get the first value that seemingly no longer exists in the SQL database. When I check the SQL database directly it has the correct updated values. If I run my script manually or even automated during the day, I seem to get the correct values. This behaviour occurs in 2 unrelated fields in 2 different databases. I have tried many things to fix this including de-indexing the field, using different populating techniques for the field (eg calculated field, auto enter calculation, script insert or set field calculation, replace field contents with calculation etc). In fact, as far as I can tell, the outdated data no longer even exists on the SQL side, yet somehow I still import these old values.

The fact that I cannot reproduce this problem during the day makes me think it is a problem on the SQL server side, but the server administrator assures me it is not. I noticed that on the ActualSQL config there is a checkbox for regional date and time settings. I doubt this is the problem, but since it does seem to be a chronology issue I have turned the option on for tonight's data capture.

If this behaviour sounds at all familiar to anyone, I would appreciate some input. I am out of ideas.

Thanks

[Jonathan Monroe]

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

[lsmall]

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

Thanks.

I am accessing SQL via the FM relationship graph. So what I am understanding, as it sounds like this could be the problem, I need to "load" the SQL table and perform a find. The way I do it now, the script does not navigate to the SQL table, so that is likely the issue. Thanks for your help.