Hi Victor,
What do you mean by "file" is that DB2's way of saying a database or table?
Can you turn on ODBC Tracing and see if it captures any reasons?
Anything in the Event Viewer on your PC?
Typically when using more than one data source you would create a Subreport and connect.
As a quick test once you connect right click on the Connection and select Options and then check off everything except Tables.
Don
Don,
Thank you for responding to my question. So to answer yours:
Victor
Event Viewer
Event Type: Error
Event Source: Application Error
Event Category: None
Event ID: 1000
Date: 06/25/12
Time: 12:15:08 PM
User: N/A
Computer: B71DE238
Description:
Faulting application crw32.exe, version 14.0.2.364, faulting module ntdll.dll, version 5.1.2600.6055, fault address 0x0004487f.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74 Applicat
0008: 69 6f 6e 20 46 61 69 6c ion Fail
0010: 75 72 65 20 20 63 72 77 ure crw
0018: 33 32 2e 65 78 65 20 31 32.exe 1
0020: 34 2e 30 2e 32 2e 33 36 4.0.2.36
0028: 34 20 69 6e 20 6e 74 64 4 in ntd
0030: 6c 6c 2e 64 6c 6c 20 35 ll.dll 5
0038: 2e 31 2e 32 36 30 30 2e .1.2600.
0040: 36 30 35 35 20 61 74 20 6055 at
0048: 6f 66 66 73 65 74 20 30 offset 0
0050: 30 30 34 34 38 37 66 0d 004487f.
0058: 0a
Great you have it working now, please mark as Answered.... I'll let the Report Resources know about this error.
Don
Don,
But I need to select from the tables and I can't do that now. I tried having tables selected and not views, and still ran into the error. How can I have tables selected without getting the error?
Victor
This is known issue:
1621593 - Crystal Reports 2011 crashes when trying to link tables in DB2 iSeries
Cause
ODBC Driver Access permission issue
Resolution
The following registry key resolves the problem:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\ODBC\
"AccessMode" as a Reg_DWORD 0x00000000 (0)
Here are the steps:
1. Go to Registry Editor (Click Start > Run > type “regedit” > OK)
2. Navigate to :
for Crystal Reports 2011 on 64 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database
for Crystal Reports 2011 on 32 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database
for Crystal Reports 2008 on 64 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite 12.0\Crystal Reports\Database
for Crystal Reports 2008 on 32 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite 12.0\Crystal Reports\Database
3. Right click the “Database” registry key > select New > Key > name the key “ODBC”
4. Right click the “ODBC” key > select New > DWord value > name it “AccessMode”
5. Make sure the value is default: 0x00000000 (0)
6. Restart the Crystal Reports and try again.
Vitaly,
Thank you for the clear instructions with regards to the Registry Editor. I succesfully followed your instructions, closed Cyrstal Reports and reopened. Crystal Reports 2011 still crashes when attempting to join two tables. Then I rebooted my computer and was still unsucessful.
I also followed your instructions at http://scn.sap.com/thread/2013152 in the Registry Editor. Still no luck. What else can we do?
By the way, we have purchased 4 additional licenses for Crystal Reports 2011 and others are having the same problem.
Victor
Message was edited by: Victor Engbrecht
Victor, this is definetely the issue as your trace shows the following statement:
“SQLStatisticsW” API generates “DIAG [42000] [IBM][System i Access ODBC Driver]Statement violates access rule: Connection is set to read only.
It is Access issue, we just need to set the registry key properly. I personally resolved it for few customers, so I know.
Here is how it should look like in the registry:
Please compare
I also think of two potential causes why the registry key is not working:
1. If you have the same key with different value in CURRENT_USER profile. Please note the value in Current User overwrites the value in Local Machine profile
2. If you have 64 bit machine, then when you right-click to create DWord value, you should select 32-bit DWord option as Crystal Reports is 32-bit application
Vitaly,
Thank you for being patient with me. I do have a 32 bit machine.
I have compared my registry to yours. One difference I see is in the Database folder. I have two folders, one is ODBC, and the other is u201CODBCu201D. Both appear to have the same settings.
I also noticed that in HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports there is no Database folder.
What else can we do?
Victor
Thanks for letting me know. I guess when the forum migrated to new Jive platform not all the information transfered properly.
The "U201C" is a Unicode for Left double quotes and "U201D" is a Unicode for the right double quotes.
If you replace the code properly you should get the same registry key.
So please right-click the key u201CODBCu201D and delete it. It should be ODBC only.
But I do not believe it was causing the problem. Crystal Reports reads only the key codes it designed to read. How familiar are you with Microsoft Process Monitor Utility? We can use this utility to check if Crystal Reports reads the registry key properly.
Hi Victor,
Try doing a Repair install of CR and make sure you are and using an account that has full Admin rights.
It could be your AV software is blocking CR from creating all of those Reg keys also.
If not check if someone else has CR installed and if so export the SAP registry keys to a file and import them into your system. If not then simply create the key as Vitaly indicated.
Should look like this:
Don
Vitaly,
I have deleted the folder u201CODBCu201D, and I have the Microsoft Process Monitor Utility open. What's next?
Victor
Add the key manually and test.
Make sure CR is closed when doing these changes or it will over write when CR is closed.
And see my reply for more tests.
Don
Don,
I uninstalled the entire Crystal Reports program, I removed the CR folders from C:\Program Files, and I removed the SAP BusinessObjects folder from the registry. Then I rebooted my computer and reinstalled the full version of Crystal Reports 2011 with Admin status.
Then I tried to use CR selecting two tables, and it crashed. I went into the registry to HKEY_LOCAL_MACHINE\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database and found no ODBC folder, so I added it according to your instructions. I reopened CR, selected two tables, click OK, and again it crashed.
I noticed that your last picture of the registry does not show a Database folder in the Crystal Reports folder. Do we need to delete the Database folder?
CR also crashes for all our other users (we just purchased five licenses for CR 2011). What should we try now?
Victor
Hi Victor,
First thing is CR should not crash because the database table is set to read only... I'm going to track that issue.
Is your XP 32 or 64 bit?
Don't delete the keys, they are there for a reason.
Thanks
Don
Don,
I have a 32-bit system, running Windows XP Pro, SP3, with all microsoft updates installed.
We're sourcing an IBM i5 AS400 running OS400, using IBM's Client Access for Windows, version 6.0.
Victor
Vitaly figured out the System DSN also had to select the option not to query the DB as Read only.
CR has always required read/write permissions on the DB, it's so the Temp DB can be written to for Cursors.
Don
It was actually Victor's team who found the cause.
If CR needs to write to a database then we need to make sure the access permitted on all layers:
- Crystal Reports
- ODBC DSN
- Firewall/network security
- Users rights on the database
- Database
On all levels we should let the “Call” to go through. The registry key is to allow Crystal Reports to make the “Call”, DSN configuration allows to pass the “call” to the database.
Gentlemen,
Thank you so much for walking through this with us. You have been most helpful in gaining a little more understanding about the registry and the configuration. I only have one question remaining. Does the change from Read-Only to Read/Call jeopardize the integrity of the data on our main IBM AS400 server?
Victor
Victor,
I guess you can verify through the ODBC tracing - Crystal Reports makes this call only at the time of adding the second table and only at report design time.
It does not make changes to the database.
It does not make the call at report runtime.
I do not believe it could jeopardize the integrity of the data
But if you want to be safe, then use a workaround I mentioned before - create a report template with two tables and turn the DSN back to read only.
You can use this template to design all reports you need. For example:
If you have report template with tables A and B, but you need to create a report with tables B, C and D, then:
What you need to do is to add all required tables and then remove tables A and B.
We've tested this workaround solution and it worked for other users.
Vitaly
Vitaly,
Thanks again to you and to Don, both for you patience and your help. Your assistance has been greatly appreciated. We can now use Crystal Reports 2011.
Victor
I have a similar problem (adding a second table in the Database Expert causes a crash) using the INTERSOLV ODBC driver for Interbase.
The registry entry solution had no effect.
This has not been a problem in previous versions of Crystal Reports.
I've used CRlogger utility to find that CR wants to make a "write" access to DB2 datasource. If this is the same issue then you should see it as well.
Please check if you have access to the following SAP Note:
1603398 - How To trace Crystal reports 2008 and 2011 using crlogger logs
https://service.sap.com/sap/support/notes/1603398
Start the utility and review the logs.
If you find a write access error, then check if the registry key is in the right place and does ot have a typo.
Plerase note DSN in ODBC Administrator may have read only access.
Finally, user may have read only access on a database level.
Vitaly
Although changing the ODBC driver works on my development machine, the installation generally within the company uses the original driver and so another driver would mean exhaustive testing before roll-out. It might be instructive to see why the original driver causes CR to fail.
I don't appear to have access to the note to which you refer. I turned on "ODBC trace" and the log had two instances of the following message:
| crw32 | d20-b98 ENTER SQLStatisticsW | ||
| HSTMT | 08C32D88 | ||
| WCHAR * | 0x00000000 [ | -3] <empty string> | |
| SWORD | -3 | ||
| WCHAR * | 0x00000000 [ | -3] <empty string> | |
| SWORD | -3 | ||
| WCHAR * | 0x0B88CC94 [ | -3] "ASSIGNMENT\ 0" | |
| SWORD | -3 | ||
| UWORD | 1 <SQL_INDEX_ALL> | ||
| UWORD | 1 <SQL_ENSURE> | ||
crw32 | d20-b98 EXIT SQLStatisticsW with return code -1 (SQL_ERROR) | ||
| HSTMT | 08C32D88 | ||
| WCHAR * | 0x00000000 [ | -3] <empty string> | |
| SWORD | -3 | ||
| WCHAR * | 0x00000000 [ | -3] <empty string> | |
| SWORD | -3 | ||
| WCHAR * | 0x0B88CC94 [ | -3] "ASSIGNMENT\ 0" | |
| SWORD | -3 | ||
| UWORD | 1 <SQL_INDEX_ALL> | ||
| UWORD | 1 <SQL_ENSURE> | ||
| DIAG [37000] [INTERSOLV][ODBC InterBase driver][InterBase]Dynamic SQL Error, SQL error code = -104, Invalid command, Data type unknown (-104) |
I would still prefer to see CRLogger logs.
Here is the content of the SAP Note:
Symptom |
Environment
Resolution
or C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86 ( 32 bit Operating Systems )
N.B: In CR 2011, the file is by default present under: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86
LOGGING_ENABLED_ASSERT = 1
LOGGING_ENABLED_RUNTIME = 30
Hi Mathew,
To find a kbase article simply type the number in the top right search box on this page. If it's older it can be found, seems to be a problem with articles released after August 1st and theya re still trying to figure out why.
That ODBC error may simply mean CR is querying the driver to see if it supports something, really need the complete log to see the flow.
So run both the CRLogger and ODBC Trace at the same time, they are going to be to large to attach to forums, and be patient it will affect performance. Once you get them then update the post and I'll send you a link to upload the files to. Please zip them up also.
Also, download this utility:
https://smpdl.sap-ag.de/~sapidp/012002523100006252882008E/sqlcon32.zip
Run the app, log on to the DB and then run a SQL Query and paste in the SQL from the report. Do you get data? If not it's a driver issue, if you do then it's a Driver/CR issue or it could be there is an indexing problem in one of the fields or it may be corrupt or simply wrong type of data. I've seen this happen before where a front end application that updates data fields does not do field type checking and writes a string into a number field. It doesn't generate an error until CR tries to report on it.
Thanks
Don
I have now upgraded to the Interbase ODBC driver version 1.00.00.100 and the issue does not occur.
Thanks for update with the solution.
