1 2 Previous Next 15 Replies Latest reply on Jun 3, 2013 6:09 PM by tikidewey

    Got OBDC Error 802 After FileMaker Server 12 Advance Restart

    tikidewey

      Summary

      Got OBDC Error 802 After FileMaker Server 12 Advance Restart

      Product

      FileMaker Server

      Version

      FileMaker Server 12 Advance

      Operating system version

      Window

      Description of the issue

      I have to restart FileMaker Server 12 Advance due to Log Viewer is not displaying the latest log. So I stop FileMaker Server 12 Advance database server and restart the server. After the server is restart, I start getting ODBC error 802 for import my data to SQL Server. Does anyone has the same problem?

        • 1. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
          Fred(CH)
               
          I start getting ODBC error 802 for import my data to SQL Server

               Hi Meeoww,

               Could the following article help you ?

          http://help.filemaker.com/app/answers/detail/a_id/11080/kw/802

               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...

               Bye, Fred

          • 2. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
            tikidewey

                 Hi Fred,

                 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.

                  

                  

                  

            • 3. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
              Fred(CH)

              Hi Meeoww,

              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 ?

                   Bye, Fred

              • 4. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                tikidewey

                     Hi Fred,

                     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.

                      

                      

                      

                      

                      

                • 5. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                  Fred(CH)

                       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

                        

                  • 6. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                    tikidewey

                         Hi 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.

                    • 7. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                      Fred(CH)

                           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 ?

                           Hard topic... crying

                      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
                      • 8. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                        tikidewey

                             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'.

                        • 9. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                          tikidewey

                               Hello Fred,

                               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.

                          • 10. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                            Fred(CH)

                                 Dear Meeoww,

                                 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.

                                  

                                 Bye, Fred

                                  

                            • 11. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                              tikidewey

                              crying

                                   Hello Fred,

                                   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.

                              crying

                              • 12. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                                Fred(CH)

                                      

                                     

                                          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.

                                      
                                     Dear, don't cry, please... wink
                                      
                                     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 OPEN
                                               File hosted by FMS, Closed on FMS, FMP is not able to connect -- FILE CLOSED
                                                    File open locally by FMP -- FILE OPEN
                                                     
                                                    So 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
                                      
                                • 13. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                                  tikidewey

                                       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.

                                        

                                  • 14. Re: Got OBDC Error 802 After FileMaker Server 12 Advance Restart
                                    Fred(CH)

                                          

                                         

                                              if HCLNY is being hosted by FMSA then why do I have to keep FMPA software OPEN.

                                         Ok, ok so i initially understood your words correctly but i definitely cannot undersand this behavior too !

                                         Moreover, thanks to your screenshot, you should not open FileMaker Pro and connect it to FileMaker Server on the same machine...

                                         This configuration is not supported by FMI.

                                         Restart your server and be shure that FileMaker Pro is not open. Then, run your test from the Admin Console (you can execute manually the schedule).

                                         And let us know...

                                         Bye, Fred

                                    1 2 Previous Next