I start getting ODBC error 802 for import my data to SQL Server
Could the following article help you ?
If not, please provide us more information about your issue. Because ODBC is a hard topic with a large panel of possibilities.
You could complete your original post with both config version info (OS, FMSA, ODBC Driver) and issue description improvements (Server side script ? FileMaker database shared as an ODBC datasource or, at contrary, FM database connecting to another ODBC DSN ?). Thus, you will get more chance that another person will be able to help you.
Please let us know...
I have already went throught the 32bit and 64bit issue with ODBC driver. I even talked to FileMaker Customer Support to clarified the issue.
At the present time, I am using 32bit OBDC driver to run the import script to SQL because I am running the script through FMS schedule.
It has been running fine since March 23, 2013.
May 26, 2013: I restart the FMS due to log Viewer is not showing the lastest log info.
May 28, 2013: FMS received a batch of data to be imported to SQL and immediately I received the Error 802. See attchment.
But when I re-process the data on May 28, FMS schedule's import script imported the data to SQL without any problem.
To confirm the Error 802 was caused by the FMS restart, I restart the FMS again at 11a on May 28 and Error 802 appear again.
I have to re-process my test data again. After that, all data are processing smoothly.
On the same day at around 11p, 802 error appear again.
May 29, 2013: I have to re-process the data and FMS schedule's import script import the data to SQL without any problem.
I just tested the 32bit FileMaker OBDC driver and it is working fine.
If you can think of anything, I greatly appreciate your help.
Thanks for you clarifications !
If i summarize to be shure i undersand:• On your FMSA, you have defined several schedules of type "FileMaker Script" named for instance "Process InvoiceLines PFP to SQL".• These schedules are each trigging a script named for instance "Process InvoiceLines PFP to SQL". Scripts are all stored on your FM database named "HCLNY".• On these different scripts, the Import Records step works fine, except just after restarting FMSA. Script error reported is 802.• 802 "Unable to open file" is described as is on FMHelp :This error can be caused by one or more of the following:• Invalid database name• File is closed in FileMaker Server• Invalid permission• We know your Import Record script step is a bit particular because it need the ODBC driver.• Reminder : All works fine except just after restarting FMSA.
Correct ?But i misunderstood one thing (excuse my bad english understanding) :After it doesn't work, which are the exact steps required to fix the problem ?
So far you have understand every steps that happened.
When I get Error 802, I have to reset the a field named 'SAP_Sent' which flag between 1 and 0 to indicate if the data is new for import to SQL via OBDC. (See attachment Sample) Initially, when I receive new data the 'SAP_Sent' field is empty. My import script will look for "" and change it to 1 to be ready to run the import script for import process.
1: new data and is needed to import to SQL via OBDC
0: when import is completed, change all the 1s to 0s to prevent ducplicate import in the next event
Somehow, my import script does not Exit Script even
Set Error Capture On
If [Get ( LastOBDCError ) > 0 ]
Exit Script 
My script will still continue to change all the 1s to 0s even it gets Error 802.
Anyway, after I cleanup all the 0 back to "", FMS script will perform the import script base on schedule with no error.
As I mentioned this morning, the Error 802 happens again last night at around 11p even without restarting the FMS.
I am lost.
Thank you for your patience and additionnal information !
But... i have more additionnal questions :• You just pasted a part of your script, that is getting the last ODBC error. But you and me acknowledge that 802 is not an ODBC error but a FMP script error, right ?• The scripts are importing data on a table occurence (TO) connected to an ODBC datasource from a FileMaker table, or it is the contrary ?• Anyways, only one FileMaker database is implied on this process ?We must keep in mind that 802 is an Open file related error...Bye, Fred
You have enlighted me that Error 802 is not ODBC error. I assumed I receive Error 802 before my script hit the import line.
I have changed the script to see if 'Exit Script' reflects for any error tonight after midnight. It looks like 1st batch of new data received after midnight always gets Error 802. I have to reprocess the data as I explained yesterday so that data will running smoothly throughtout the day. I also checked to see if another process that can cause the midnight error but found nothing.
Yes, scripts are importing data on a table occurence (TO) connected to an ODBC datasource from a FileMaker table.
But I am not so sure what you mean 'Only 1 FileMaker database is implied on this process'.
In my case the FM database is HCLNY and the tables are as follows:
Invoice + InvoiceLinesHCL (See attachment)
Invoice + InvoiceLinesPF
When you say Error 802 is an Open file related error. Are you saying cannot open HCLNY or Invoice + InvoiceLinesXXX
I appreciated your patience and detail explaination.
Mmhh... in this case i think it is the part "Invalid permission" of 802 that is concerned, but it is speculation.
On your Import Record script step, on the option [Specify data source], if you reselect it, all seem correct and you have defined on second dialog both Username and Password with the box to memorize checked, Right ?
As side-comment :
Before the import step, your error verification is bad because the Set Error Capture [ON] step will certainly be performed without any error, so it will clear a potential error reported by the previous step. Indeed, you must always test errors just after the critical step or store the result on a variable first to test it later.And after import step, i think you can test as is :If [ Get ( LastError ) + Get ( LastODBCError ) ]...Thus you can test on the same time FM errors and ODBC error.Bye, Fred
If it is an 'Invalid Permission' or Error 802 then why only right after midnight. I can be sure that everything runs fine all day today until midnight comes again. So I have renew my permission at midnight??
I confirmed that Username and Password are both memorized in the script.
I will take your advise on the test FM error and ODBC error and see if I have to renew my permission again at midnight.
You are right 'Hard Topic'.
I have found out what is the cause of the Error 802.
I have done 3 tests as follows:
Test 1: Test FileMaker 32 bit ODBC with HCLNY open and Test Passed
Test 2: Test FileMaker 32 bit ODBC with HCLNY closed by keep FMA open and Test Passed
Test 3: Test FileMaker 32 bit ODBC with HCLNY and FMA closed and Test Failed
It looks like I have to keep FMA open, otherwise; I will run into the Error 802 problem.
Do you think you can tell what have I done wrong after the FMS restart? I even re-hosted HCLNY in FMS.
Cool : we move forward… but i am perplexed :
HCLNY is the database where your schedule is trigging the script, right ?
If true, when HCLNY is closed, that i don't understand because HCLNY is a hosted file, no any script can be executed on it !
And to clarify, when you tell "FMA" you want talk about FMPA (FileMaker Pro Advanced), right ?
If true, that is strange too : the particularity of server-side scripts is precisely that they are executed by FileMaker Server (here, FMSA).
I think you made a little confusion. I cannot imagine how the desktop app state can be implied here.
If all true, 802 error is clearly generated because your file is closed. Be shure the files implied are open when you schedule a server-side FileMaker script.
Moreover, i cannot see any reason why your hosted databases are closed as regularly.
I am just as perplexed as you are.
HCLNY is a hosted file in FMS (FileMaker Server Advance 12) and all script triggers are executed on the FMS schedule.
I have no problem importing from SQL to FMS even with HCLNY and FMA (FileMaker Pro Advance 12) CLOSED.
i only have problem importing to SQL from FMS with HCLNY and FMA CLOSED. At this time, the only way to run this task without getting Error 802 is to keep FMA OPEN.
To me, this mean HCLNY is being hosted by FMA instead of FMS for importing to SQL while HCLNY is being hosted by FMS for import from SQL.
I don't have this situation before until May 26 after FMS is restarted.
BTW, I have impletment your suggested command after the import statement "If [ Get ( LastError ) + Get ( LastODBCError ) ]" and now my 0s and 1s are straighten out. Thanks for the advice. But without figuring out the HCLNY hosted file issue, I am a bird with one leg only.
I think we are not according on terms. It is probably because my approximative english. I meant file closed "server-side"...When you are saying that the file is open, do you mean by FMPA ? Because, you know, if the file is hosted by FMSA, client connexion status should not have any influence on scheduled scripts. The status of a file, Open or Closed is host-depending, not client-depending. Examples :File hosted by FMS, Open on FMS, FMP not connected -- FILE OPENFile hosted by FMS, Closed on FMS, FMP is not able to connect -- FILE CLOSEDFile open locally by FMP -- FILE OPENSo in your case you must take a look on the File status on FMS Admin console only. See my screenshot when i did a test...Bye, Fred
HCLNY database is always hosted on FileMaker 12 Server Advance.
This is the part that you and me don't understand is:
if HCLNY is being hosted by FMSA then why do I have to keep FMPA software OPEN.
As soon as I CLOSE FMPA software, I will failed in my order process because Error 802 is unable to open file and it is because the script cannot connect to HCLNY after username and password.
See if you understand me better this time. Sorry for the confusion. I hope the pictures tell a thousand words.
Don't worry about your English, I fully understand what you are saying. I have to thank you for your patience too.