Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 297 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Navision 3.7 and ODBC problems with Crystal Reports 11
PKS
Member Posts: 3
Can someone pls help me here, I have come to the end of the line with the Crystal Reports support people and have spotted a number of threads which outline the problem I am experiencing, but as yet no resolution for me.
My Customer has NAV 3.7 but is not using MS SQL server, & I therefore access the db via the supplied NAV 3.7 C/ODBC.
Crystal Reports ver 8.5 works fine with this but i now require the functionality of Crystal 11.
Using the C/ODBC data source as "System DSN", in Crystal Database Expert I have created ODBC connections using both ODBC RDO (which works like a dream with Crystal ver 8.5 for the virtually identical report) and OLE DB ADO with Crystal 11 (now upgraded to the very latest Crystal 11.5 with all updates), this ADO using "Microsoft OLE DB Provider for ODBC drivers" with the C/ODBC DSN.
My initial problem with Crystal 11 and the ODBC RDO connection was that I could not add various Navision tables, here's what I would get:
" Database Connector error: '22003:[Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver]Out of range [Database Vebdor Code: 1011 ]' ".
I initially got around this by creating the report in Crystal 8.5 and adding the tables, then opening the report in Crystal 11 and developing from there. This was using the ODBC RDO connection and my CR11 rpt appeared to work initially. But then whenever I ran or refreshed the report I got the error message above. One baffling observation is that I would get the error but if I then chose to save the report as "save with data" crystal would happily go generate the "save with data" as if i had refreshed the report, so it has some other clever way of retrieving off the db.
Crystal support advised that "Crystal ver 9 and above required unicode standard 3.0 whereas C/ODBC for NAV 3.7 is unicode standard 2.0 which is not compliant".
I obtained the N/ODBC driver for NAV 4 but this appeared not to be backwards compatible as it would not allow a connection to NAV 3.7.
Back to the original C/ODBC which I then tried two ways, first with the DSN "options" "identifiers" as "default" (which was "all except DOT"), and also with identifiers as "a-z,A-Z,0-9_" (or similar). I experienced the same "out of range" errors with the RDO connection and then tried with ADO connections. With an ADO connection using the C/ODBC DSN with identifiers "a-z,A-Z,0-9_" I had some progress in that I was finally able to add ANY Navision table, but each time I attempt to run or refresh my report I now get:
"Failed to retrieve data from database.
Details: ADO Error Code 0x
Source: [Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver] Unexpected extra token: INNER
SQL State: 37000
Native Error: [Database Vendor Code: 1017]"
One step forwards and two steps back ! From my previous limited experience with Crystal and ADO my recollection is that retrieving too much data, or the db growing larger, often resulted in an error when running a report.
ARGHHH... am tearing my hair out and would greatly appreciate some expert assistance with this.
Many thanks.
NB. One suggestion on the various threads I have located and which I have not pursued as customer is not keen due to security issues on creating an MS Access database and ODBC to the NAV db & link to this from Crystal.
My Customer has NAV 3.7 but is not using MS SQL server, & I therefore access the db via the supplied NAV 3.7 C/ODBC.
Crystal Reports ver 8.5 works fine with this but i now require the functionality of Crystal 11.
Using the C/ODBC data source as "System DSN", in Crystal Database Expert I have created ODBC connections using both ODBC RDO (which works like a dream with Crystal ver 8.5 for the virtually identical report) and OLE DB ADO with Crystal 11 (now upgraded to the very latest Crystal 11.5 with all updates), this ADO using "Microsoft OLE DB Provider for ODBC drivers" with the C/ODBC DSN.
My initial problem with Crystal 11 and the ODBC RDO connection was that I could not add various Navision tables, here's what I would get:
" Database Connector error: '22003:[Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver]Out of range [Database Vebdor Code: 1011 ]' ".
I initially got around this by creating the report in Crystal 8.5 and adding the tables, then opening the report in Crystal 11 and developing from there. This was using the ODBC RDO connection and my CR11 rpt appeared to work initially. But then whenever I ran or refreshed the report I got the error message above. One baffling observation is that I would get the error but if I then chose to save the report as "save with data" crystal would happily go generate the "save with data" as if i had refreshed the report, so it has some other clever way of retrieving off the db.
Crystal support advised that "Crystal ver 9 and above required unicode standard 3.0 whereas C/ODBC for NAV 3.7 is unicode standard 2.0 which is not compliant".
I obtained the N/ODBC driver for NAV 4 but this appeared not to be backwards compatible as it would not allow a connection to NAV 3.7.
Back to the original C/ODBC which I then tried two ways, first with the DSN "options" "identifiers" as "default" (which was "all except DOT"), and also with identifiers as "a-z,A-Z,0-9_" (or similar). I experienced the same "out of range" errors with the RDO connection and then tried with ADO connections. With an ADO connection using the C/ODBC DSN with identifiers "a-z,A-Z,0-9_" I had some progress in that I was finally able to add ANY Navision table, but each time I attempt to run or refresh my report I now get:
"Failed to retrieve data from database.
Details: ADO Error Code 0x
Source: [Microsoft Business Solutions ApS][Microsoft Business Solutions-Navision ODBC Driver] Unexpected extra token: INNER
SQL State: 37000
Native Error: [Database Vendor Code: 1017]"
One step forwards and two steps back ! From my previous limited experience with Crystal and ADO my recollection is that retrieving too much data, or the db growing larger, often resulted in an error when running a report.
ARGHHH... am tearing my hair out and would greatly appreciate some expert assistance with this.
Many thanks.
NB. One suggestion on the various threads I have located and which I have not pursued as customer is not keen due to security issues on creating an MS Access database and ODBC to the NAV db & link to this from Crystal.
0
Comments
Just curious if everything works like a charm with 8.5 what does ver 11 offer that it is worth such a headache?
http://www.BiloBeauty.com
http://www.autismspeaks.org
the Crystal support people were incorrect in advising a driver issue with C/ODBC for NAV 3.7 for Crystal 9 and above. And Crystal 9 has the same functionality that I was after that is in Crystal 11 but not in 8.5 (namely, the ability to include report sub-total formula's summarised as AVERAGE in a cross tab summary). YAY !
Will await customer upgrading from NAV3.7 before revisiting Crystal 11.
I do have one further query for you, is there some simple way I can possibly add my customised crystal report to the navision 3.7 menu ?
They removed support for ODBC that does not support ODBC v3 specifications. This stopped many odbc data sources from working.
There is no work around that i know of (after 10 years working with crystal reports)
I am going through this process with a customer at the moment and the solution is to upgrade the backend database to v4 of navision. this can be done without breaking the 3.7 data structures, but all clients need to be upgraded to v4 of the client.
Crystal 9 is no longer supported by Business Objects (who are the new owners of crystal reports). 10 runs out this year as well.
Just FYI, as the crystal support people are pretty hopeless.
Knutsford Enterprises Ltd