I am connecting to Navision from ASP.NET with the C/ODBC driver, and I receive the following error: [Navision a/s][Navision Attain ODBC Driver]ISAM error I have connected successfully many times, and now it seems I cannot connect at all. I also received this error intermittenly early on. I cannot seem to determine when I will get it and when I will not. At one point, I could kill the asp worker process between each connection and it would connect. I have used both the .NET Microsoft ODBC connection as well as the ADODB connection objects. Any help would be greatly appreciated.
Hi, C/ODBC driver is not thread safe, meaning that multiple concurrent requests from multiple web users are more likely to crash the driver or return errors. This is what’s causing the intermittent errors. Try using lengthy queries to regenerate these intermittent errors. If the driver is failing every time, then you have something else wrong; such as different version numbers for C/ODBC and the server. They must have the same version. I have used C/ODBC version 2.60.B and 2.60.F, so I don’t know if these issues were fixed in later versions, but it doesn’t seem so. I have not used ASP extensively with Navision, so I am not sure if others have successfully used it with the C/ODBC driver. One option you have is to upgrade to the MS SQL Server option, this way you will be using MS SQL ODBC Driver and bypass the buggy C/ODBC driver all together. Obviously, MS SQL ODBC Driver is stable. One drawback is that flow fields are not available when using MS SQL ODBC Driver. Without upgrading, you can create a COM Object DLL that you would call from ASP, and this DLL would serialize requests to the C/ODBC driver. The variable that contains the COM Object DLL should be in an ASP application level variable. This way only one instance of the DLL is loaded. Experience taught me that C/ODBC almost never crashes if you execute one SQL statement after another, but I have not rigorously tested that. One way to reduce these intermittent errors in C/ODBC driver is by making the query run as quickly as possible. One common problem is using “ORDER BY” incorrectly. When used incorrectly; your query will take a long time because a temporary index will be created. Whenever you want to select records from a table, make sure that the list of fields after “ORDER BY” begins with the same sequence as one of the table keys. You can see the list of keys for a given table by clicking on “Sort” in the toolbar while displaying a form containing that table. For example, if you want to display a sales order to a customer online, then you should use: SELECT * FROM “Sales Header” WHERE “No.”=‘SO0312001’ ORDER BY “Document Type”, “No.” If you use ORDER BY “No.”, then your query will take a long time to execute because C/ODBC will create a temporary index. If you look at the list of keys for Sales Header table, you will find that none of them starts with “No.”, at least in the default configuration. You don’t have to include all the fields in a given key, you can omit any part of the key that is to the right, just make sure that you begin with the same sequence as it appears in a given key. When replying; please include Attain version that you are using. Also, tell me if you are trying to insert/update recods into Navision. Some fields compatibilty errors only surface when you begin to update the database, espacially with CODE, DATE, and DATETIME fields.
C/ODBC is indeed buggy, but it is actually thread safe - it takes a critial section at each API entry point. But in any case an ISAM error is a controlled error condition - not a crash. I suggest you run an ODBC trace (available for any ODBC driver in the ODBC control panel applet) and check the output file. You can see, with some examination, which ODBC API function is last called before this error is thrown. E.g. SQLBindParameter(), SQLGetData(), SQLExecDirect() etc. This might (!) give a clue as to the nature of the problem. The bottom line is that C/ODBC is distictly unprepared for the OLEDB provider for ODBC in both its .NET and non .NET forms. It is simply not tested against them. Navision 4 will give a much-improved ODBC driver, with extended SQL support and more wide-spread testing. It will be a direct replacement for todays C/ODBC.
Naji & Robert, Thank you much for your suggestions. We are currently using Navision Attain 3.60, and I have confirmed the driver and program versions are the same. I am doing a select statement with grouping and no order by. The driver seems to be more trouble than it is worth, and we have learned that switching to the SQL Server version of Navision may work best for us. At this point, we have decided to abandon C/ODBC, but if we revisit it in the future I will do some tracing and further debugging. Thanks again for your help. Cory