Running a Script from a Schedule with ODBC import

[etsmarines]

I have a script that i run that pulls records into a FM solution via ODBC connection.

 

I can run this script fine and the ODBC import works fine when i run it locally and manually run the script. But when i set the script to run via the scheduler from the server that it is hosted on i get an 802 error

 

Schedule "import lansweeper" scripting error (802) at "Inventory Dev 22APR2015 MASS UPDATE TEST : 0 Import Lansweeper Data : Import Records".

 

I can see the ODBC connection on the script. What is going on here?

 

Thanks!

 

ERik

[Wim Decorte]

Did you create a DNS with exactly the same name on the FMS machine?  Same name as the DSN on your local machine.

[etsmarines]

I opened the server that file is hosted on. I gave ODBC 32 bit the same name in System DSN as the ODBC 64 bit system DSN....

 

now it is working perfectly!!

 

Thanks!!

[laurentades]

I am facing  exactly the same issue but on Mac OS.

The fix around 32-bit/64-bit doe snot seem to apply to Mac OS setup as per FileMaker's KB.

The most annoying part is that performing the same on FMS12 works great but fails on FMS13. 

I have sought support on the ODBC driver vendor's side but 802 is not an ODBC error...

Now I am stuck having to run this script manually instead of a schedule

Help Please!

[Wim Decorte]

The 32/64 bit thing only applies to Windows.

Are you sure the names of the DSN match exactly on your local machine and the FMS box?  On the FMS box, is the DSN in the System DSN tab?

[laurentades]

Are you sure the names of the DSN match exactly on your local machine and the FMS box?

Yes: in this particular case I am running the FMP on the same box as FMS, so its the same DSN altogether.

On the FMS box, is the DSN in the System DSN tab?

Yes, System DSN.

Error 802 is "Unable to open file" - if this is a reliable error description. I guess that if the error was ODBC related the code would be 1400-1413. Therefore is it a problem with the file where the data is imported ? The script runs in a shell file which only has TO's pointing to another data only file. It could be a permission issue but I am running it all with [Full Access] Admin account and with "Full Access Privileges" in the script. Also, i tried importing to a table within the file itself and 802 still comes along the way.

 

Edited August 26, 2015 by laurentades
More details

[Wim Decorte]

What are you importing from?  What database and where is it?

[laurentades]

Importing records from oracle 11g DB sitting on the same LAN (same Gb Swich...). It works perfectly fine when running from FMP....

[Wim Decorte]

Are you using PSoS or just a server-side schedule?

[laurentades]

Both and with equal result...

Let me rephrase that : not using both in the same script but rather whether i use either i get the same result.

[Wim Decorte]

Ok, still thinking permissions issue.  If you use the same System DSN, and given that Oracle 11g is a supported ESS back-end, you can you add add a table or a view to a test database using ESS?

[laurentades]

Yes, can add an ESS table from this source in relationship graph.

Made a script to add a record: works well when running from FMP. Generates no record when running PSoS from FMP or as schedule on FMS: weird thing : does not even return an error even though i did capture it in the script.

 

[laurentades]

Another point for what it's worth:

I use the same DSN to get data back to Oracle using Execute SQL script step. Works fine with FMP, PSoS and Schedules...

Therefore issue would seem to be more around Import Records in relation with ODBC data source than DSN itself....

[laurentades]

Ah, ah!

I am now trying a much more basic test : importing a table to my file from another file on the server : works ok on FMP and getting error 100 on PSoS/Schedule. I guess those errors 100 and 802 can correlate somehow but for the life of me I can't think of one reason why that would happen....

[Wim Decorte]

Ah, ah!

I am now trying a much more basic test : importing a table to my file from another file on the server : works ok on FMP and getting error 100 on PSoS/Schedule. I guess those errors 100 and 802 can correlate somehow but for the life of me I can't think of one reason why that would happen....

Hold on now, that's not relevant.  (Provided that you are trying to import from another FM file).

FMS can not be a client so it can not import from another FM file, that's not supported.  It can import from other types of files (including ODBC sources) but it can not import from a FM file.

[laurentades]

Hmmm, ok... now I remember this fact. So the 802 issue still remains

[laurentades]

Ok, looks like I managed to pin it down:

Error 802 does indeed relate to a file (data source) wich FMS cannot open in this case and as Wim Decorte was thinking it is a permission issue.  And indeed on the ODBC data source side rather than the target import FM file.

After lots of thinking i just thought about checking credentials in the OS X keychain to see that upon initial DSN setup and test, the ODBC Manager only kept credentials in the session keychain and not in the system's. Copying the keychain item across to the system keychain simply solved the issue which in the meantime caused me to lose the few hair i had left on my head as my avatar shows it...

Not sure whether that was so obvious but I cannot remember having to watch this specifically on earlier setups...

As a side message to whom it may concern -  FM Dev or ActualTech Dev - logging the authentication exception somewhere in logs would have been helpful.