Make sure you have installed the ODBC drivers on your server machine.
You're not saying what version of FM. @Philip_Jaffe is correct: it does work provided you have the correct ODBC driver on the server machine and a DSN based on that. Since FMS is only 64-bit it needs to be in the 64-bit ODBC data sources control panel.
(This is where there can be some confusion if your FMP is 32-bit then you would have it in the 32-bit section on your machine - if you then create a DSN on the server in the 32-bit section then FMS does not see it).
The last thing to consider: the DSN has to be named exactly the same on the server as it is / was on the machine where you have set it up when you created the script. As long as the name matches and the DSN is in the right place on FMS then it will just work.
Thank you Philip & Wim,
Update: I'm running FMS15. I have installed the 64bit ODBC driver. I also have FMP15 installed on the server. When I run the script with FMP on the server it works. But it still does not work as a scheduled script. The script is below. Any help would be appreciated.
Set Error Capture [ On ]
Go to Layout [ “CS_Sales” (CS_Sales) ]
Set Variable [ $lastDate ; Value: Last ( CS_Sales::Date ) ]
If [ IsEmpty ( $lastDate ) ]
Set Variable [ $lastDate ; Value: GetAsDate ( "12/31/2016" ) ]
Set Variable [ $nextDate ; Value: Let ( [ ~month = Month($lastDate) ; ~day = Day($lastDate) ; ~year = Year($lastDate) ] ; Date( ~month ; ~day + 1 ; ~year ) ) ]
Set Variable [ $query ; Value: "SELECT " & ¶ & "DATE_FORMAT(od.stamp, '%c/%e/%Y') AS 'Date', " & ¶ & "SUM(oi.total) AS 'Sales', " & ¶ & "COUNT(DISTINCT(od.order_num)) AS 'Orders' " & ¶ & ¶ & "FROM order_items AS oi " & ¶ & ¶ & "LEFT JOIN order_data AS od ON od.order_num = oi.or…" ]
Import Records [ With dialog: Off ; DSN: odbc_source; Calculated SQL Text: $query ; Update matching; Mac Roman ]
Show All Records
The FMS event log will give you an error number if an error was generated in the execution of the script, that can be a clue. For server-side and PSoS scripts you may want to build your own logging mechanism to check how far the script gets.
Keep in mind that server-side scheduled scripts and PSoS scripts also first run the OnOpen script and anything that comes with that. That can catch you out and the error may actually be in those script sequences and not in the script you intend to run.
1 of 1 people found this helpful
Any chance you created a User DSN instead of a System DSN? That would cause the behavior you described.
As Wim suggested, creating a method to log errors in your server-side scripts will make debugging easier. In addition to logging Get(LastError), also log Get(LastExternalErrorDetail) for more detailed info from the ODBC driver.