Question for dealing with external data through ADO

jelittlejohn

Hi,

I am currently using an ADO automation object to import order information from an external MySQL database. This works great. However, every so often an order is imported with data that does not pass internal NAV validation and an error is thrown. This is fine within NAV. I have error handling setup so the routine can move on to the next order and rights a record telling the user there was an error with the order in question. The problem is that when these errors arise, the ADO object never makes it to the Close command and that particular connection is left hanging open. Over time this causes some problems with the external database.

I know that I should grab the external data, assign it to a NAV variable and immediately close the connection but I have set up the whole thing to just grab each piece of external data from the ADO recordset only as it's needed and wait til processing is complete to close it. I would love to avoid the work involved with changing this.

Does anyone know of any way that I can close all open ADO or ODBC connection from a NAV session or from the client machine as a whole? I still have control from the calling codeunit of may error handling routine but I no longer have access to the variable name of the ADO session.

Thanks in advance for any ideas.

ara3n

You could connect to the other database and get a list of non active session and issue a kill statement?

Is the other db sql?

jelittlejohn

It is MySQL.

I like the idea and will look into it. If you have any helpful hints or code snippets (even if they are for SQL), I would appreciate it. I might consider creating t his function and having it run once daily in the middle of the night.

Thanks again.