SQL Server and ODBC version 4.0.22100 Issues

I am using SQL 2000 SP2 (same issue with SQL 2005 SP2) on Windows Server 2003 SP2. I do not have any Navision components installed on my machine other than the ODBC driver and a Dynamics NAV database.

I have set up my ODBC driver as per the following article:

http://www.statssheet.com/articles/article46665.html

What I want to do is create Views based on T-SQL statements like the following:

SELECT * FROM OPENQUERY(NAV_CHRONUS,‘SELECT * FROM Customer’)

I have the following issues:If the database has a user-name or password then I get error messages saying that the user name/password combination is incorrect (when they are correct).

If I reference a database with no user name or password the first one or two queries work okay but then after some time has elapsed (typically a few minutes) I get messages similar to the following (when executing SQL queries which had just worked fine):

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider ‘MSDASQL’ reported an error.

[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed]

[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed]

[OLE/DB provider returned message: [Simba][SimbaEngine ODBC Driver][DRM File Library]The operating system returned the error (183):Cannot create a file when that file already exists.

Error: 183]

OLE DB error trace [OLE/DB Provider ‘MSDASQL’ IDBInitialize::Initialize returned 0x80004005: ].

Once things have gone belly-up then I cannot even link the tables in Access anymore and get messages like:

ODBC- call failed

[Simba][Simba Engine ODBC Driver][DRM File Library]The program is unable to connect to the server’s process. This is because the server is running as a service using a system account. Please run the client using the network instead.

Error: 899 S1000-4999[Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectASttr failed (#0).

I’m not sure I fully understand your issue but maybe some of my experiences with the ODBC might help.

Have you got the correct version of the NODBC installed? Is CHRONUS not spelt CRONUS?

It looks like you have had the trace running? When running the trace I would expect it to record the error on the NAV odbc driver with slightly different terminology - as in not [Microsoft][ODBC Driver Manager]… I could be wrong…

I have had problems in the past where I am trying to drop and re-establish a connection over and over again to the database over ODBC. The system does not like this and after a while says it cannot connect with a SQLSetConnectAttr failed being logged in the trace. Is your process logging in once and then running the queries etc it needs to, or is it constantly dropping the connection and re-establishing it over and over again in quick succession. In the later scenario we have had problems, as have others with the NAV ODBC Driver.

The scenario we had was a third party supplier had some scanning software that when a document was scanned it sent data into a table in NAV via ODBC. The problem was that the scanning software, connected to NAV and inserted the record then dropped the connection, then connected again and updated a field, then disconnected, then connected again and updated another field and so on until the scanned metadata was updated in the record correctly. This process took less than 10 seconds but the constant dropping and re-connecting screwed the ODBC. It effectively still thought the connection was already opened. The code was changed to open the connection, run the 10 second process from the scanner and then close the connection. All now works fine.

I have also seen where partners have setup the ODBC User in the database as being called ADMIN or something similar and then the problems seem to go away. However, I cannot give any reason why that should work apart from pure magic?!

I hope this helps you some way to resolving your problem.

Hello JohnFrench,

I encountered the same problem: T-SQL procedures using OPENQUERY on an N/ODBC-datasource. It’s a batch job running once every night and querying lots of data from navision db, and additionally a procedure refreshing one order, which is called many times a day from navision side and then querying some data from nav db.

SQL Server does run these jobs fine, but only up to a certain moment. From then any further query on this linked server errors with:

Der OLE DB-Anbieter “MSDASQL” für den Verbindungsserver “…” hat die Meldung “[Microsoft][ODBC Driver Manager] Fehler bei SQLSetConnectAttr-Aufruf für Treiber” zurückgeben.
Der OLE DB-Anbieter “MSDASQL” für den Verbindungsserver “…” hat die Meldung “[Microsoft][ODBC Driver Manager] Fehler bei SQLSetConnectAttr-Aufruf für Treiber” zurückgeben.
Der OLE DB-Anbieter “MSDASQL” für den Verbindungsserver “…” hat die Meldung "[Simba][SimbaEngine ODBC Driver][DRM File Library]The operating system returned the error (183):Eine Datei kann nicht erstellt werden, wenn sie bereits vorhanden ist.

Error: 183" zurückgeben.
Meldung 7303, Ebene 16, Status 1, Zeile 1
Das Datenquellenobjekt des OLE DB-Anbieters “MSDASQL” für den Verbindungsserver “…” kann nicht initialisiert werden.

(It’s the same error message as above - only German language and referring to the linked server connection which name was deleted for the posting.)

Any help or ideas would be greatly appreciated.

Best regards

mweber

John:

I am encountering the exact same issue/behavior as you described with a SQL server linked server and a product called Timberline accouting. We recently upgraded to a new release and now the linked servers no longer connect to Timberline’s tables via the DSN that was working fine prior to the upgrade.

I also experieinced the same inconsistent behavior that you describe in that I can sometime initially connect and query a Timberline table via SQL server and the linked server connection, but subsequent queries result in the errors you list above, referencing Simba, Simba Engine ODBC, etc.

Did you ever find a resolution to your issue? I would love to hear it if you did, or even a workaround.

Thank you very much,

Peter Havlis

Hi,

Same question: Did you find a solution to establish a linked server connection to navision.

We have the same problem and NAV3.7 works fine…

Best regards

Christian