Bug in N/ODBC - cannot connect to one company after

Miklos_Hollender

I know you cannot connect to two companies at the same time with N/ODBC, but I cannot even connect to one company after the other.

I mean I have a Native Database, I have an SQL Server and want to copy tables from the Native Database for reporting into the SQL Server every night via DROP TABLE.... SELECT * INTO.... FROM OPENQUERY(NODBC_Comp1 ... If I try it one after the other with different companies it results in an authentication error, but if I restart my SQL Server (basically the "ODBC Client") in between it works i.e. it is something along the lines of after the first query the connection to the first company is not being closed properly.

This pretty much prevents consoldating our data so any ideas are welcome how to work this bug around i.e. how to close the N/ODBC connection after the OPENQUERY for one company is finished in order to be able to query another company. Restarting the SQL Server is not an option because you cannot do that automatically and it has to run automatically every night.

Miklos_Hollender

Just bringing up the topic. Such a critical bug surely has some workaround? All I want to do is to really close the connection somehow after the OPENQUERY ran in order to run it for the second company, third...

kanchanmanoj

Hi,

Did u checked the test connection after creating the ODBC connection???

vaprog

[Never mind! I did not read the initial post thorowly enough.]

If all else fails, try to install a second NAV ODBC using odbcconf.exe

Miklos_Hollender

vaprog: great idea, thanks! Any hint on what command-line options to use? I checked MSDN and used Driver=nodbc.dll and APILevel=2 and nothing else and it did not work i.e. could not see it in the driver list. Probably I need to set some other options. I unpacked the MSI installer but could not find it.

Kanchanmanoj: of course, but did not help. Both connections work if I restart the SQL server (which is calling the OPENQUERY from the native database i.e. this is the ODBC client) between calls. The problem is this needs to run every night and I cannot manually restart the server.

Saalek

Hi

I know there is a problem with ODBC, that don't close server connection.
Did you tried to use C/FRONT to transfer data ? I used it to transfer data betwen different DDBB and I think is faster than ODBC.

Bye

vaprog

Here's what I used in a .cmd-File in the copied driver directory

odbcconf /A {INSTALLDRIVER "NAV 5.0|Driver=%~dp0nodbc.dll|Setup=%~dp0nodbccfg.cfg|FileUsage=0"} /Lv "%~dpn0.log"

I can't remember whether I added/changed some registry settings afterwards.

I recommend you run it from an elevated command prompt.

Miklos_Hollender

Thanks! Do I need to rename the files as well f.e. nodbccfg.cfg --> dp0nodbccfg.cfg?

Miklos_Hollender

Success! Admin please move it to tips and tricks!

odbcconf.exe didn't work for me but I just copied the driver folder (microsoft dynamics nav / 60 / odbc) and hacked the registry directly:

in registry under ODBCINST.INI:
New key "Microsoft Dynamics NAV Driver 2" and two strings inside it Driver and Setup, and also under the ODBC Drivers key added the string "Microsoft Dynamics NAV Driver 2" and the value "Installed". After that I could create a data source and connect to first one company then after the other.

thanks again

hardest thing in my career - they really don't teach this sort of registry-hacking in the business school