Report states "No rowset was returned..." after deployment

[PManion]

Our product uses Crystal Reports as our report designer. Since our customers want to be able to customize these reports, we are requiring them also to purchase Crystal Reports. We have a problem with one of our reports which is causing some difficulty with redeploying the program to another computer.

The database we are using is SQL Server; we are connecting via OLE DB. I know that Crystal Reports embeds a reference to both the SQL Server server and the database inside each report. We would like the reports to use the local SQL Server server and database for their data source.

The problem is that the reports work properly on the test machine but one of them fails when redeployed to another computer. All of our reports are based on tables or views except one which was based on a stored procedure. The stored procedure originally required two parameters. I managed to get this working on a deployed machine, but then the report would prompt the user with a dialog box for the parameters. I didn't want this to happen because the default dialog box was rather ungainly, and because one of the parameters was meaningless to the user anyway. I wanted to pass a single index value to the report and have it open a dataset based on a query on that value. Finally I bypassed the parameters problem by having the program store the value in a table and having the stored procedure read it from there. So now the stored procedure would be called without parameters. After making this change I got the report working on my test machine. After deploying it to another machine the report would fail with this error: "No rowset was returned for this table, query, or procedure." I thought that since this is the only report based on a stored procedure rather than a table or view, the problem was being caused by using a stored procedure. So I changed the stored procedure to write its data to a regular table and pointed the report to the table. After this fix, I still find it works on my test machine and fails with the "no rowset" error on a deployed machine. So that wasn't the answer.

Any ideas? I am still working on debugging this. Any suggestions you may have would be very helpful.

 

[Roadkill150]

Hello,
Just a quick thought but try this.. at the beginning of your stored procedure insert SET NOCOUNT ON right after the BEGIN statement. Also make sure that there are no hyphens in your database or table names. Lastly, check and make sure the drivers you are using are the same on both machines.
Hope this helps!
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[PManion]

Bruce,

Thanks for the reply. I saw an article in the CR Knowledge Base that made similar suggestions. But this problem doesn't seem to be isolated to stored procedures. I am now pointing the report to a table, and it still fails.

I may be on the wrong tack altogether. I think the problem may be deployment of a report using a different SQL Server data server and database. So my question becomes, how do you redeploy a report correctly? That is, having a report using a local SQL Server data server correctly on one computer, how do you move it to another computer with its own SQL Server without installing Crystal Reports with the application? How does the report know where to look for its data after it is moved?

Should I repost this question under a new topic, since my question has changed?

Patrick Manion
pmanion@jobtime.com

 

[is47501]

Bruce,

Here are some thoughts, As suggested before by roadkill150, make sure that the database connectivity dlls are the same on all machines. On your second e-mail, I agree it sounds like the report once deployed to the new machine is still trying to access the original database. I have used Stored Procedures in many reports and have had a lot of good luck with them so don't give up on them. My reports are imbedded in a VB application and run against a production server. As the application is migrated to Dev, Test and Production environments the application has to change the database and server that the report connects to.
The application uses the SetLogonInfo Method to handle this.

How are you deploying and running the Report?
Are the Databases the same Name on each machine?
Are the Stored Procedures/Tables the Same Name and
structure on Both machines?
Is security the same on both Machines?

Sorry to answer your question with more questions, but I hope it helps.

Britt