CR 8.5 Pro Crashes when using Stored Proc On Great Plains

[darwin101]

Hello all

I have written a sql server 2k stored proc using Great Plains Dynamics 7.5. It works just fine, and the ODBC to it works fine also. When I try to add the proc to a CR template, Crystal crashes with this error message
"The instruction at "0x73dd1351" referenced memory at "0x000000004". The memory could not be "read".
I have rebuilt the ODBC several times, it Test fine, but even with a blank / new report Crystal crashes each time I try to add that database using ODBC. There are no spaces or special characters in the Database name. The ODBC is created using a domain admin account, so are the reports. Anyone have any ideas?

Thanks

 

[FVTrainer]

Just a couple basic stored proc questions: Does the stored proc return a recordset? Is that the last thing it does? Does the stored proc use any cursors?

Another thing you might do is test the stored proc in another tool, like creating a pass-through query in MS Acesss, just to see it's behavior using the same odbc connection and see if that gives you a better idea of what might be going on. Sorry I don't have a real answer for you.

 

[darwin101]

The stored proc does return a record set,the same errors happen on every machine I try it on. The return is the just before the proc drops the temp table. There are no cursors. I installed CR ver 9 and it works fine with this database/stored proc. This may be my only workaround, I really dont want to use CE 9 but if it works!

thanks

 

[FVTrainer]

Were you using an OEM version of Crystal that shipped with Great Plains, or an off-the-shelf version. Which version was it?

 

[darwin101]

I was using a full version from Seagate CR 8.5 Pro. The version 9 came with Great Plains. I know special character in DB names, table name and column can cause an issue, but I have not seen any of those in this DB.
Thanks

 

[FVTrainer]

So the version 9 works but the version 8.5 doesn't? Are 8.5 and 9 installed on the same machine, or are they on different machines. (I'm not sure ifthat matters, because 8.5 and 9 should play well together.) Also, have you tried running the stored proc as a pass-through query in Access (or Excel, for that matter)? Have you been successful with that?

One last question: in your original post, you said it crashes when you add it to a CR template. What exactly do you mean by a CR template? Are you starting the report as a blank report or are you using the Report Expert?

Anyone else that would like to jump in here with an answer or idea would be welcome!

 

[darwin101]

Ver 9 works, 8.5 does not. They are installed on different machines. Have not tried using Access or Excel, never did that before. CR crashes every way I have tried to select ODBC connection using CR ver 8.5, not 9.0. This seems to be an ODBC connection error, but using ver 9 with the same odbc the crash does not happen. I have applied all patches and updates.
Thanks for your time and mental powers

 

[FVTrainer]

The only reason I'm suggesting using Access to test the stored proc is that sometimes you get a more descriptive error message in another application. If you look at the help file in Acess for the topic "Pass-Through Query" you should see how to call a stored proc in access (it's something like EXEC database.owner.procname).

Another thing you might do, just to see what happens in the 8.5 report is change the driver to p2ssql (pdssql.dll). When you go to add the datasource to the report, Select "More Datasources" and look for Microsoft SQL Server. Supply the server and login info and then pick the stored proc from the database. CR 9 doesn't support the native MS SQL driver, but 8.5 does.

 

[PTW]

I had a similar problem with Crystal Reports 8.5 and Great Plains 7.5--Crystal would crash each time I tried to add a stored procedure. Here is the resolution posted on the Microsoft Great Plains Techknowledge site. It solved my problem:

If the stored procedure you want to use is part of a Dynamics/eEnterprise company database, or any database with a large amount of stored procedures, Crystal Reports will crash when it tries to return a list of all the stored procedures.

In order to get around this issue, restrict the stored procedures that Crystal returns in the 'Add Database' window. To do this:

1. Open the Options window by going to File | Options | Database tab. This is where you select to show stored procedures.

2. Uncheck Tables and Views, check Stored Procedures and check Re-prompt user when connecting.

3. There is a text box with a prompt 'Table name LIKE:'. Use this as you use a 'like' query in SQL Server. Example - if your stored procedure is named "sp_MyDataPull" you can restrict it to return only stored procedures that start with "sp_" by entering "sp_%" in the 'Table name LIKE:' box. This works for only returning certain tables or stored procedures, so if you are creating an Inventory report in Dynamics you could restrict it to only show the tables starting with "IV".