cancel
Showing results for 
Search instead for 
Did you mean: 

Executing a report with stored procedure causes error

Former Member
0 Kudos

Hello,

I've got a .NET application where a report gets generated from an .rpt template using Crystal Reports. A template has been created using the fields from stored procedure. While creating the template, I've been working with my DEV environment DB server, and with that server the .rpt has been saved. Within the Designer, I can preview the report, all working fine.

Using the template with my application, if I set in the code for crystal connection to use the same DB server as has been saved with the .rpt, the report runs and I do get the result in my application as expected. But if I move my code to other environment, using another DB server (but not changing anything in the .rpt), while running the report in the application I get the following error:

Exception: CrystalDecisions.CrystalReports.Engine.InternalException

Message: Failed to open a rowset.

Details: ADO Error Code: 0x

Source: Microsoft OLE DB Provider for SQL Server

Description: Could not find stored procedure 'BreachOfAgg'.

SQL State: 42000

Native Error:

Failed to open a rowset.

Error in File C:\Windows\TEMP\Breach of Aggregate Stop Loss {196B84E5-7DAE-465E-A12D-5C70DDC04CE5}.rpt:

Failed to open a rowset.

Source: CrystalDecisions.ReportAppServer.DataSetConversion

at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportOptions options)

at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToHttpResponse(ExportOptions options, HttpResponse response, Boolean asAttachment, String attachmentName)

at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToHttpResponse(ExportFormatType formatType, HttpResponse response, Boolean asAttachment, String attachmentName)

at IFG.ReportsModule.Reporter.CrystalReporter.Export(OutputType outputType, HttpResponse response, String reportName, Boolean asAttachment)

at IFG.ReportsModule.Reporter.CrystalReporter.Export(OutputType outputType, HttpResponse response, String reportName)

at IFG.ReportsModule.Proxies.CrystalReporterProxy.ShowResult(OutputType outputType, HttpContext context, CrystalReporter cr)

at IFG.ReportsModule.Proxies.CrystalReporterProxy.ExecuteReport(ParametersValues parameters, OutputType outputType, HttpContext context)

at IFG.Web.GetReport.GenerateReport(Int64 reportID) in d:\Jure\PSL\iFish\IFISHRSF\IFISHRSF0100\Root\5. Development\IFISH Portal\SDRMA New\Portal\web\admin\RSF\GetReport.aspx.cs:line 82

at IFG.Web.GetReport.Page_Load(Object sender, EventArgs e) in d:\Jure\PSL\iFish\IFISHRSF\IFISHRSF0100\Root\5. Development\IFISH Portal\SDRMA New\Portal\web\admin\RSF\GetReport.aspx.cs:line 48

Nested Exception

Exception: System.Runtime.InteropServices.COMException

Message: Failed to open a rowset.

Details: ADO Error Code: 0x

Source: Microsoft OLE DB Provider for SQL Server

Description: Could not find stored procedure 'BreachOfAgg'.

SQL State: 42000

Native Error:

Failed to open a rowset.

Error in File C:\Windows\TEMP\Breach of Aggregate Stop Loss {196B84E5-7DAE-465E-A12D-5C70DDC04CE5}.rpt:

Failed to open a rowset.

at CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

If I open in the Crystal Designer the rpt and change the DB to my new server, and then run the report in the application using that same new server, the report runs ok and I do get the results. As well, I don't have this problem using tables or views in the report. Independent of what server has been used while created the .rpt in the Designer, I get the report ran and presented according to the server that I point to within my code.

I hope someone can help me out with thi.

Thank you.

Jure

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

So bottom line is you create a report off of one server and you magically expect the report to know you are now wanting it to go to another server....

Try adding code to set location to the new server info.

Search for "SetLocation" and you'll find "how to"

Don

Former Member
0 Kudos

Hi, maybe was not clear enough, sorry about that, but I am dynamically setting the DB for the report within the code. And if in the template I use tables/views, no error occurs, when switching in the code between the DB servers (the application can against different DBs, one at the time still, but I don't want to change anything within the rpt when switching between DBs). The only time I've receive an error is if stored procedures are used in the template, and the DB server I used when creating the rpt is not the same one as used in the application.

I hope this helps, will look into your suggestion still.

Jure

0 Kudos

That helps.

What DB are you using and what version?

What version of CR RAS are you using?

Create an new report and get the info from the original source and then running in debug compare the connection properties to the new DB Server and see what changed and then adjust.

If you are using CR 2008 and MS SQL Server 2008 then install at least Fix Pack 3.4 and use the SQL Server Native 10 driver as the DB source. Update your reports to use it also.

Don

Former Member
0 Kudos

Hi Don, thank you for the answer. I'm using SQL Server 2008, and even having two instances of a server on the same computer, I'm getting the error. I'll take a look at what you suggest also. Not sure if I understand it well (am quite new to Crystal Reports), but if anything I'll post reply.

Jure

0 Kudos

When you installed MS 2008 you now must install the Client Tools which installs an updated client dll.

It's called SQLNCLI10.dll, when you create your connection CR prompts you for the driver, select the Native 10 driver, after you install the client tools. Now create your report and save it, then using your code it should work.

CR is not aware of the new driver name so we had issues with Store Procedures. Using the new driver and patch should resolve your problems.

Don

Former Member
0 Kudos

Hi Don,

as for Fix Pack 3.4, I've noticed that there's later version for it, Fix Pack 3.6. I guess 3.6 should work as well, right?

I did some testing and having Crystal Reports 2011, using the SQL NAtive Client 10, I did get rid of the error. Now, I hope it works for my other environment with Crystal Reports 2008.

Thank you.

Jure

0 Kudos

Correct. Any FP after 3.4 will have the fix in it...

Thanks again

Don

PS - please remember to mark posts as answered.

Former Member
0 Kudos

Hi,

I've updated the CR2008 with the Fix Pack 3.6, and the problem persists. However, if I use CR2011, the problem disappears. Any idea why? Why even with FP3.6 it does not work? Am getting the same error.

Thanks.

0 Kudos

Open the report up in CR Designer and click on Database, Set Location.... Expand the + sign beside to Server and look to see what the Driver indicates in both CR versions.

Both should say SQLNCLI10.dll.

Then look at the SQL Query and verify when pointing each instance that the connection is fully qualified, could be they are getting mixed.

Use Profile in MS Management studio on both and see if when running the reports they are going to the correct instance or possible both.

Also, your original post indicates you are using the OLE DB Provider and not the Native 10 driver in your report:

Source: Microsoft OLE DB Provider for SQL Server

Former Member
0 Kudos

Hi Don,

could not find any differences between pointing the application to one DB or another. Neither could find any difference when using a report done with 2011, comparing to reports done with 2008.

Just wondering, by "the connection is fully qualified" for SQL Query, you mean? Any other ideas?

Thanks.

jure

0 Kudos

Fully qualified means:

Server1.dbo.database

The new location should be Server2.dbo.database.

Don

Former Member
0 Kudos

Hi Don, don't know if there's anything more to do, tried debugging,SQL profiler, checking any differences between using reports done with CR2008, done with CR2011, and could not determine any difference, between the connections used and the way connecting.

Don't know, could it be any DLL updates needed?

Thanks anyway.

Jure

0 Kudos

Hi Jure,

All I can suggest now is you try this [app|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333533353333333933323331%7D.do]:

It will get the connection if you need.

If that doesn't work the purchase a support and Rep can connect to your PC to see what is going on...

Thanks

Don

Former Member
0 Kudos

HI Don,

could not get the application running, windows encountering a problem. Was wondering though, could it be that I'm using the crystal DLLs version 10.5.x? And should be using versions 12.0.x?

I was trying to find DLLs v12.0.x, but no luck. Would you know, if I could download them from somewhere? Or which application, version should they be included in?

Thanks.

jure

0 Kudos

Click [Here|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333533353333333933323331%7D.do]

Answers (1)

Answers (1)

Former Member
0 Kudos

HI Don, I did download the application from the link you've sent already in one of the previous posts, but when running it, am straight away getting the windows error about the application stopped working. Thanks anyway. I think I'll just try to get the client to get the CR2011. The license will probably be cheaper than spending more time on the problem.

jure