Crystal Reports, Stored Procedure Issue

[Malleyo]

I'm using CR 8.5, VB6, and SQL Server 7.

I took over the maintenance of an application when one of our employees left the company. (Let's call the app App1). Shortly after, I created another app (let's call this one App2). Both apps use Active Data to pass a recordset to an existing Crystal Report. App1 was written a few years ago, when the company was still using Crystal Reports 6.0. We have since upgraded to Crystal Reports 8.5 (which is what we were using when App2 was written). App1 has been distributed to nearly every computer within our company, with version 6.0 of all the Crystal Reports dlls. When I wrote App2, I created the reports with version 8.5 of the dlls. When App2 was distributed to a few computers at the company, some of the reports from App1 stopped working (Error 20527 SQL Server Error). I tracked it down to the CR dll p2smon.dll. If version 6 is installed, both programs work perfectly. If version 8.5 is installed, App 2 works perfectly, but App1 doesn't. I tracked it down even further to a recordset that was created using a Stored Procedure that contains an If statement or a Case statement. Another weird thing is that if I create the recordset the exact same way, calling the exact same Stored Procedure, but using ADO instead of RDO, it works fine. Unfortunately, it's impossible for me to upgrade the program to ADO, so I have to try to find a solution for RDO. At the moment, I'm going to leave the old dll in place. But that could cause problems in the future if I create another report that relies on features in the newer dll that aren't supported in old dll.

I have searched every forum I could find, I have done every Google search I could think of, I have even tried to contact Crystal Decisions (we don't have any free tech support and I can't convice the company that it's worth paying for it).

Has anyone come across this problem (or something similar)? Is it a bug in Crystal Reports? Is there a difference in the way the recordset is created when calling a Stored Procedure vs embedded SQL? Any thoughts, websites, etc would be greatly appreciated!!

Thanks in advance!

Fiona

 

[paulmarr]

Fiona,

I have limited experience with third party apps using Crystal - but my experiences are that Crystal 6 and Crystal 8.5 cannot live on the same computer disk partition without malfunctioning due to the dlls.

My short term fix would be to make sure App1 is not on the same partition as App2 - that way the dlls wont clash. The problem with these previous versions are that they named the dll files the same even though they are very different.
Hope this helps

paulmarr

 

[Malleyo]

Thanks for your response. When App2 was installed, it replaced all the version 6 dlls with version 8.5. One work around that I found and implemented on 2 computers was to put a zero-length file named App1.exe.local and version 6 of p2smon.dll into the same file as the App1.exe. This tells App1 to look in it's own directory for the dll and App2 can look in the system directory for it's dll (version 8.5 of p2smon.dll). Surprisingly, I have not had any other problems in App1 after upgrading all the dlls to version 8.5.

 

[paulmarr]

Good Fiona - Im glad we worked that one out!

One problem you may encounter (I did) is the export dlls are not the same and seem to clash - dont know if your app allows exporting. Try it out.

Cheers,

paulmarr

 

[Malleyo]

The work around doesn't solve my problem. There's still the fact that App1 needs to have an older version of the dlls installed in order to work properly. At this point, the olny solution I can see is either change the program to ADO or rewrite the SP's so that they don't use an IF or CASE. Unfortunately, both of those solutions require many many hours of coding considering how extensive the program is. It's just not possible for me to implement either of those solutions at this time. I'm hoping to find out why Crystal is behaving this way. I want to know if the problem is within the recordset, the stored procedure, the dll, RDO, or the report itself. I want to fix the problem, not use a work around!

Paulmarr, I REALLY appreciate all the help you've been. I've gotten more insight on this problem from you than from any other forum posting or website I've visited.

As for the exporting part... App1 does allow exporting to a select few people while App2 doesn't allow it at all (Although some of the export dlls may have gotten mixed into the setup file for App2). I'll keep my eye on it though!

 

[paulmarr]

Fiona,

I can identify with your frustration. If you want to fix the problem once and for all - I would update App1 by converting all Crystal 6 and 8.5 reports to the same version.

Im sure I am talking about alot of work - but the problem would then be solved for once and for all!

Take care!

paulmarr

 

[Malleyo]

Paulmarr,

From what I read, to upgrade the reports, all I would have to do is open them in 8.5 and save them, correct? I did that and I still get the error. The problem (I believe) lies in the 8.5 p2smon.dll. I believe it's a bug in Crystal Reports. Unfortunately, I can't contact Crystal about it since our company has no free support and they don't feel that this problem is worth paying for support (since I've found a temporary workaround).