What to look out for when exporting data using ODBC?

MOLAP

I'm using Navision Dynamics 5.0 and need to export all the financial data into my datawarehouse on a regular basis (1 time daily). And therefore I don't want to use csv-files as exporting method.

My vendors says:

"We don´t recommend that you go directly in the SQL and collect data, because it can result in corruption of data or you can lock tables while exporting."

Is that true?


According to Microsoft themselves the answer to the question is the following:

"ODBC access to Microsoft Navision is fully supported for Read operations although write operations need careful attention as the business logic is bypassed (for example triggers are not executed)."

Source: page 15 in http://www.navisionguider.dk/downloads/Nav_IntegrationGuide1.2.pdf

Anyone with experiences in using ODBC for read operations only?
Does it disturb the write operations in any critical way? (is write operations made impossible, or is data destroyed)?
Or is it just a performance issue? (slower writes, while you're exporting/reading tons of data)?
I guess I could experience dirty reads (read outdated data), but write operations should still be possible for others?

bbrown

This depends on the transaction isolation level used by the ODBC connection. Read_Committed (SQL Default) will issue shared lockes during read operations. These have potential to impact write operations. Read_Uncommitted (default for NAV) does not issue any locks.

rhpnt

MOLAP wrote:

I'm using Navision Dynamics 5.0 and need to export all the financial data into my datawarehouse on a regular basis (1 time daily). And therefore I don't want to use csv-files as exporting method.

My vendors says:

"We don´t recommend that you go directly in the SQL and collect data, because it can result in corruption of data or you can lock tables while exporting."

Is that true?

Exporting data directly from the SQL Server for BI purposes is THE BEST and I repeat THE BEST option! I never heard that read queries can "corrupt" data or lock tables!? Are these guys seriuos!?