This is Joe replying to my own post. I have since switched the file format to XLS and it seems to work just fine. I am not sure why the change has occured, but I am going to accept it for now. If I have time, I will go back and troubleshoot some more. I will post whatever I discover back to the forum here. :smileyvery-happy:
yay, forum works in FF (not ie8 though).
Anyways, have you checked the SQL server DSN is using database auth and not AD user? Nice sticking point with a lot of ODBC clients. Reminds me because I ran into it wirth linked tables and FMP 11, as well as php of course.
Also, make sure the DSN has the correct default DB, not sue if your script has use xxx; go in it already. Then of course there is the DB perms, but you said it worked when you ran it as a client. How did you log into the fmp script (again, db auth or AD auth)?
This post seems to be ancient, but I may resort to the "Robot" file (server side scheduled script - import) to run imports via odbc as they seem to not really work as a server script. My server script was set to run a script step that imports via odbc every 2 hours. It has been reporting success for over a year. I just found out that the import has not run in 10 days even though success is reported (via email confirmations). I am guessing that somebody was runing the script manually still everyday and so we did not notice that the server script was not actually running.
I guess a robot file is like we used to do in filemaker 5...a computer runs macros or windows tasks to open and close a filemaker file that has a perform action on open script step that executes a script in another filemaker database.
Bogus confirmation email:
FileMaker Server 220.127.116.11 on fmpserver reported the following event:
2012-02-17 11:20:13.550 -0800 Information 150 fmpserver Schedule "Inventory Import" completed.
FileMaker Server automatically sent you this email. Contact the administrator if you no longer want to receive these notifications:
I did just see that my script step had "unknown" layout in it while I was verifying server compatibility. This could have cause the issue of not working but ther server still reported success.
the Unknown Layout seems to be why the records were not updated via odbc server script step even though it reported completion. Re linking the import to a layout with all the fields fixed this issue for us.
Just guessing that the_9th might have fdropped a field from his layout that exported a flat csv and that is what happened to that script not really completing successfully.