Navision ODBC connection to fdb file

axelrod_eric

I am a Business Intelligence consultant working on a Data Migration for an SAP implementation. Navision (v3.7) is one of the many legacy systems, and we don't have a SME on the IT side. The datastore for this Navision installation is the fdb database file.

I obtained the installer for the entire Navision 3.7 application suite, including the ODBC connector, and successfully made a connection to the DB via ODBC.

However, I discovered a major problem. Many of the Navision column names are not compliant with the ODBC standard. Not only do many column names contain spaces and special characters, many are also more than 30 characters long. As a result, the ODBC driver throws an error on every table which contains an "illegal" column name.

I don't know how much, if any, custom development was done in the Navision implementation, but it seems strange that nearly every table has at least one illegal column name.

Is this a common problem with extracting Navision data via the supplied ODBC driver? Does a solution exist?

Thanks for your help,

Eric Axelrod
Consultant, Business Intelligence

ara3n

Is this a data migration tasks?
If yes, I suggest to make a nav backup. Tools->backup.


Open finsql.exe. Create a new database on a SQL server (Files->database->New)

Restore the .fbk file Tools->restore.

Do all the odbc connection you need directly from sql.
As far as ODBC Standard. you can have fields with spaces in it you just need to put it within [ ]
And fields in Nav cannot be longer than 30 characters.

ara3n

NAV ODBC actually shows you the Caption (language specific caption), not Field names.

So if the translation is not correct, then you could get fields that are longer than 30 characters.

If you want to know if it was customized, You can design the table. Tools->Object designer.->table button->find the table and click Design Button. Find the field and look at ID it has.

Anything from 50K to 99K are customizations.

axelrod_eric

ara3n wrote:

Is this a data migration tasks?
If yes, I suggest to make a nav backup. Tools->backup.

This is a data migration task, but the Navision system is still operating in production and will continue to be for the next 6 months. Another consultant is already working on performing a backup and restoring to SQL Server -- this should allow us to generate ERD's and perform data analysis, but it is hardly an ideal solution. We will need to perform the backup and restore to SQL Server every time we want a fresh snapshot of the data. Ideally, we would connect directly to the fdb file in production.

ara3n wrote:

As far as ODBC Standard. you can have fields with spaces in it you just need to put it within [ ]

The spaces themselves aren't the issue. Decimals, forward slashes, backslashes, hyphens, etc. in column names are the issue.

ara3n wrote:

And fields in Nav cannot be longer than 30 characters.

I have identified numerous columns in the production database which are between 32 and 35 characters. There appears to be at least one column name in excess of 30 characters in almost every Navision table we have tried to query -- and the ODBC driver appears to be truncating the column name down to 30 characters in the stream, which subsequently results in duplicate column names within the same table.

axelrod_eric

ara3n wrote:

NAV ODBC actually shows you the Caption (language specific caption), not Field names.
So if the translation is not correct, then you could get fields that are longer than 30 characters.

Does the supplied Microsoft ODBC driver refer to columns by Caption or Field Name?

ara3n wrote:

If you want to know if it was customized, You can design the table. Tools->Object designer.->table button->find the table and click Design Button. Find the field and look at ID it has.
Anything from 50K to 99K are customizations.

This may be interesting, but it is relevant?

ara3n

Does the supplied Microsoft ODBC driver refer to columns by Caption or Field Name?

The ODBC driver sends the captions

This may be interesting, but it is relevant?

Second issue is not relevant, just wanted to let you know.

ara3n

As far as analyzing the data on SQL, you won't find an ERD. None of the relationships are stored on SQL.

I suggest to get a NAV consultant to help you with data migration.

Although the tables are easy to understand, you'll spend a lot of unnecessary time learning a product on customers expense.

At least get the client to direct you what tables have the data.

axelrod_eric

ara3n wrote:

The ODBC driver sends the captions

So, the ODBC driver sends the captions, some of which are over 30 characters in length. What's the solution to overcome the ODBC limit?

ara3n

remove the captions. Tool->Language->Export

axelrod_eric

ara3n wrote:

As far as analyzing the data on SQL, you won't find an ERD. None of the relationships are stored on SQL.

I am not looking for an ERD... I am looking to create one. As long as the data model follows some standards (PK's are in Ordinal Position 0, FK's inherit their names from PK's and are NOT in ordinal position 0) then my data modeling tool can successfully infer the relationships.

I suggest to get a NAV consultant to help you with data migration.

Although the tables are easy to understand, you'll spend a lot of unnecessary time learning a product on customers expense.

Point taken. Navision, however, is one of many not-well-understood legacy systems involved in the migration for which the client does not have an IT SME. Data Analysis (including data model analysis) is already a huge part of the project.

At least get the client to direct you what tables have the data.

That is already a task assigned to the Functional Area Owners. They will have to find out what functional areas each legacy system (including Navision) is being used for... and we should be able to hunt down the tables.

axelrod_eric

ara3n wrote:

remove the captions. Tool->Language->Export

This is something I can try on a copy of the fdb file, which is all I have at this point. The production database, however, is off-limits... our level of authorization does not allow us to make changes to the legacy systems.

Is this the only way to pull data from an FDB file through the ODBC connection?

ara3n

There are other methods, Such as Dataports to extract the data into TAB or comma delimited file.

That would be the easiest and fastest way to do it.

Once the dataport has been written, you can export it and import it into production and run it at any time to extract the data into a flat file.

axelrod_eric

ara3n wrote:

There are other methods, Such as Dataports to extract the data into TAB or comma delimited file.

That would be the easiest and fastest way to do it.

Once the dataport has been written, you can export it and import it into production and run it at any time to extract the data into a flat file.

That sounds like a workable solution. Can the dataports be executed remotely without any third-party plugins or custom code?

And, while that may be a good solution to get the data out of the production system, we still have the issue with the metadata. Is there an INFORMATION_SCHEMA or an ALL_TABLES object which contains table names, column names, datatypes, primary keys, foreign keys, alternate keys, etc?

ara3n

axelrod_eric wrote:

That sounds like a workable solution. Can the dataports be executed remotely without any third-party plugins or custom code?

You will need to login with Navision client and run the dataports.

And, while that may be a good solution to get the data out of the production system, we still have the issue with the metadata. Is there an INFORMATION_SCHEMA or an ALL_TABLES object which contains table names, column names, datatypes, primary keys, foreign keys, alternate keys, etc?

There is an ER Diagram in the download section for Standard Nav. Any customization they have done, you have to run the table from the client and find the foreign keys.

ara3n

Here is the link

http://www.mibuso.com/dlinfo.asp?FileID=398

axelrod_eric

ara3n wrote:

You will need to login with Navision client and run the dataports.

I'll give this a try next week. I was trying to experiment with dataports earlier in the week, but my user account has insufficient permission to create a dataport. What roles do I need? Oh, and this is a German version of Navision, so all of the Role names are in German...

ara3n wrote:

There is an ER Diagram in the download section for Standard Nav. Any customization they have done, you have to run the table from the client and find the foreign keys.

An ERD is great, but it is only useful in the Data Analysis stage of the project. Having access to the table definitions in an electronic form will make the ETL immensely simpler... otherwise we will have to manually retype the column definitions for input into the ETL application. Normally the ETL application reads the table definitions from the source through a standard connector and populates its own metadata repository. It can read column names from flat files and create column name metadata, but datatypes will also be required.

Suggestions?


By the way, thanks for all your help. You are saving my team a lot of time.

axelrod_eric

ara3n wrote:

You will need to login with Navision client and run the dataports.

When I log into the Navision client I select Tools -> Object Designer -> Dataport button -> New button the client throws the attached error and crashes.

There are errors in the text conversion (text no. 1963-0 does not exist in the .stx file). Internal Error: 47-1

Any ideas?

ara3n

I've found this thread.

viewtopic.php?f=23&t=23480&hilit=1963



I suggest to get a later version of Nav.

axelrod_eric

Trying a different route...

Does anyone know of a 3rd party ODBC driver which may have a higher name length limit?

liyasker

My Question also relevant to the Connecting Navision FDB File.

Requirement : " Connect Navision FDB File from MS sqlserver SqlServer Intergration Services(SSIS)

I am working MS SqlServer 2005 SP3 and I have Installed N ODBC 4.0 SP1. Is it Feasible?
I did some work around to achieve my requirement.


Work Around 1.

I Created ODBC User DSN using NODBC. And I refered this ODBC Data Source in SSIS. Test Connection is Success. But i am not able to see any table in the SSIS DataReader Source.
i was stopped in this level.


Work Around 2.

Using the Same "User DSN" i can connect Navision FDB from MS XL and i import the Data.

And using MS Sqlserver 2005 Import Wizard I am able to see the tables and columns But while performing the operation

i am getting this error...



"

- Validating (Error)

Messages

Error 0xc0047062: Data Flow Task: System.Data.Odbc.OdbcException
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) (SQL Server Import and Export Wizard)

Error 0xc0047017: Data Flow Task: component "Source - Query" (1) failed validation and returned error code 0x80131937.
(SQL Server Import and Export Wizard)

Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)

Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)

"

I hope that SQL Import Wizard works then it should work in SSIS DataReader Source as well . Connecting Navision FDB file from SSIS 2005 sp3 using NODBC 4.0 sp1 is Fissible?Can any one explain me to overcome?

David_Singleton

axelrod_eric wrote:

Trying a different route...

Does anyone know of a 3rd party ODBC driver which may have a higher name length limit?

Why don't you use English language for the ODBC connection, that would probably eliminate the issue with the strange lengths of the German captions. As to the special characters, the NODBC driver allows you to convert them to underscore.The thing is that what you are trying to do is a pretty simple task, and probably a good NAV consultant will resolve it very quickly, in the long run you will probably save a lot of time/money going that route.