Solved

Crystal Reports rowset error during remote OLE DB connection to SQL Server

Posted on 2009-04-03
23
1,251 Views
Last Modified: 2012-05-06
I have a simple Crystal Reports file (i.e. no db code) that connects to a stored procedure in MS SQL Server 2000. The stored procedure returns a table. The report takes the table and groups by each the first three columns. Everything works great until I try to use the Select Expert on one of the group columns.

I consult for a small client. I create the report on my home XP machine and connect via OLE DB to a database on my local machine. The client wants to be able to use the Select Expert to narrow down the results. When I used the Select Expert at home, it worked. But, when I sent the file to the client (who connects over a local network) it refused to work when using the Select Expert*. Trying to narrow the results churns for a while and then gives a "Failed to open a rowset" error. Followed by "Query Engine Error: 'Error Code: 0x800a0bcd'" error.

Further, when I connect to the clients database via VPN, it has the same problem. So the report works locally, but not remotely. This is using the exact same database configuration- although their data has more recent updates than mine. Note that it DOES work for the client without the Select Expert.

I tried many many things to solve the problem. Finally, I tried removing the groupings. It worked! For some reason, the group by feature is not working with the Select Expert when connecting to the remote database.

I saw a similar issue for another person that involves making the group columns into formula fields, but this is an onerous and hacky solution. Plus I have formula fields already based on the groups and it would break all of them. I'm not sure the rest of the report could be replicated so I really don't want to go down this path if possible.

Anyone know why this is happening?

*Actually the Select expert works IF you generate the data first and then do not re-get the data from the server. But this is unacceptable.

0
Comment
Question by:wgcltd
  • 14
  • 4
  • 3
  • +2
23 Comments
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
How big is your result set? Do you have any issues connecting to the DB via SQL Query Analyser and running the SP? Does it return all results?
0
 

Author Comment

by:wgcltd
Comment Utility
Records returned depends on parameters for the stored proc (which can be set from the report side). However results can be quite large. A typical one I am testing returns 12961 rows. Yes, all results are returned by the Query Analyser. Like I said, I get all the results *except* if I use the Select Expert. And the Select Expert will work, provided A. The data is on a local machine. or B. The data already exists in the form. But as soon as I try to refresh the data on a remote connection, it craps out.
0
 

Author Comment

by:wgcltd
Comment Utility
Went ahead and changed params to return a much smaller rowset (675), but it still has the same problem.
0
 
LVL 6

Expert Comment

by:agandau
Comment Utility
Have you (or can you) run a trace through SQL Server profiler to see any difference in the behavior?  

You might get differences in the connection properties during Audit Login or in the sp_prepares.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are you grouping in the report or the stored procedure?

mlmcc
0
 

Author Comment

by:wgcltd
Comment Utility
The stored procedure does a series of joins and some expression logic and puts them into a temporary table. But in the end, it's simply returning a rowset. The report does the grouping after getting the rowset, and therein lies the issue. If I just display the rowset on the report, then I can use the Select Expert to narrow the result set. But, if I group by one of the columns (only on a remote session!) then I get the rowset errors.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Is that in the designer?

mlmcc
0
 

Author Comment

by:wgcltd
Comment Utility
It's in the designer. I use the Select Expert feature of the report designer. Actually it's not me. My client wants to have selection control over the result set. I provide him the report and the stored procedure to return data from his database. He wants to further narrow the result set using the Select Expert feature (the hand with the three balls icon in the report designer). But it is not working with the connection. When he (or I for that matter) try to narrow the result set in the Crystal Reports report designer, the data won't fetch. It gives an error. If I do it on my local machine, it works fine. I don't know how to make it more clear than that.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Sorry.  I don't know what the problem might be, but FWIW ...

 I've got a report that uses a SQL stored procedure (running on SQL Server 2005, but it was originally on SQL Server 2000).  The report groups on one of the columns from the stored procedure, and it also tests that column in the record selection, based on a parameter.  I have no problem running that report over a VPN connection.

 This sounds like pretty much the same situation you described and it works for me.  I'm running the report from CR and using CR 10.

 Which version of CR are you using?  I've kind of skimmed through the messages here and if you said, I didn't see it.

 Could you attach a copy of your report to a message here so that we can take a look at it?  EE only accepts certain file extensions and RPT isn't one of them.  Just change the extension to one that EE accepts, like TXT, and u/l that, perhaps with a note that the extension needs to be changed back to RPT.

 James
0
 

Author Comment

by:wgcltd
Comment Utility
Can't send the report because it involves proprietary pricing information from my client. I'm using Crystal Reports 10 by the way. Since nobody seems to find this a familiar problem, it may be an issue with their server. Perhaps it's not the remote connection at all, but rather a misconfiguration problem with their install of SQL 2000? The only difficulty I have with that theory is that you can actually get the data from the remote source, THEN use the Selection Expert, and it works fine. It seems to me the Select Expert goes to work after the data is collected. Unless perhaps once you ask for the data again it decides to send the Select Expert settings to the server machine as a select statement? I dunno, it's driving me crazy. I wish they would upgrade to 2005 at least, but they have other software that is 2000 only.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
If you just u/l the report, without any data, that wouldn't have any pricing on it, would it?

 > It seems to me the Select Expert goes to work after the data is collected.

 When using a stored procedure, that seems to be how it works.  When reading tables directly, CR will pass the conditions that it can to the server, but not with procedures.

 > Unless perhaps once you ask for the data again it decides to send the
 > Select Expert settings to the server machine as a select statement?

 Not as far as I can tell.  Look under Database > "Show SQL Query" and it should show what CR is passing to the server.  When using a stored procedure, all that I expect you'll see is the procedure name and the parameters.

 James
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I agree.  A stored procedure is "compliled" by the database and as such you can't pass the select filter to it.

mlmcc
0
 

Author Comment

by:wgcltd
Comment Utility
Yes, but could it not send "Select * from EXEC StoredProcedure A,B,C WHERE XYZ" or some such to the sever as a query? That might produce some kind of problem. Although, checking it in the report yields no such query.

HOWEVER:

The (working) query in the local report looks like this:

{CALL "Executive_Query_Development"."dbo"."ThreeGroupProfitComparison";1('Salesperson', 'Division', 'Customer', 'Sales', {ts '2008-01-01 00:00:00'}, {ts '2008-01-31 21:59:59'}, {ts '2009-01-01 00:00:00'}, {ts '2009-01-31 23:59:59'})}

And the (non-working) query in the remote report looks like this:

"Executive_Query"."dbo"."ThreeGroupProfitComparison";1 'Salesperson', 'Division', 'Customer', 'Sales', {ts '2008-01-01 00:00:00'}, {ts '2008-01-31 21:59:59'}, {ts '2009-01-01 00:00:00'}, {ts '2009-01-31 23:59:59'}

Could the differences in the call method indicate an issue?

I gotta say too. When I first created the report I used a SP called "ProfitComparisonDivision" and then changed a few times and eventually changed to "ThreeGroupProfitComparison"  But, the table name in the Crystal report always shows up as "ProfitComparisionDivision" no matter how much I update it to a different SP. Could this be an issue as well? Is there a way to permanently change this table name in the report?

I'm attaching the report as James0628 and mimcc suggested. It is set as a txt file and will need changed to a rpt file. It is the broken one. Hopefully it will work when changed back.

Let me know what y'all think. Thanks!
Comparison-Report-SE.txt
0
 

Author Comment

by:wgcltd
Comment Utility
One more thing I noticed. The ordering of the report fields when using the Select Expert differs slightly from the ordering of the columns in the SP. Could it be getting confused about mapping fields to columns?
0
 

Author Comment

by:wgcltd
Comment Utility
Noticed that if I remove all groupings and just use Record Sort Expert on one field, the report also fails. It will only fail if there is some kind of sorting going on. It is not particular to grouping.
0
 

Author Comment

by:wgcltd
Comment Utility
One more: If I sort or group by a formula field, the problem does not occur.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> Yes, but could it not send "Select * from EXEC StoredProcedure
 > A,B,C WHERE XYZ" or some such to the sever as a query?

 I suppose it's technically possible that it could construct a query that executed the stored procedure and filtered the results, but, AFAIK, it doesn't do anything like that.

 It's interesting that you see CALL in one place, but not the other, but I don't know what, if any, significance that has.  Also, you said that the one with CALL was the one that worked, but I did not see CALL in the report that I checked, so, if there was a difference, I would expect the one without CALL to be the one that worked.  

 Forgive the obvious, but are you sure that you're running the same version of the same report in each location?  Seeing two different queries (one with CALL and one without) makes me wonder.

 > The ordering of the report fields when using the Select Expert
 > differs slightly from the ordering of the columns in the SP.

 In the Select Expert (where you create a record selection), the first thing in the list are the fields used in the report.  You're not looking at those instead of the fields in the procedure, are you?  Forgetting the Select Expert for the moment, are the fields shown in the Field Explorer in the same order as the columns in the stored procedure?  AFAIK, they should be, although it's not something I normally pay that much attention to.

 If you think the fields are not in order, try a "verify database" (Database > "Verify Database").  Perhaps the "definition" that the report has for the procedure is out of date.  That might explain some of this.

 Also, I suppose there's the general question of whether there's any chance that you're connecting to different db's, with different versions of the stored procedure.  You could try changing the stored procedure (eg. adding a new column) and seeing if the change shows up in the report in each location.


 When you change the datasource in a report, CR does not change the name that's shown in the report.  If you want to see the actual datasource name, go to Database > "Set Datasource Location" and drill down under the datasource name.
 To change the name shown in the report, go to Database > "Database Expert".  Click on the procedure name on the right side of the screen, then click again, or right-click and select Rename.  Then you can edit the name.  You're only changing the name shown in the report, not the actual datasource name.


 > If I sort or group by a formula field, the problem does not occur.

 Hmm.  What if you create a formula that just contains the field that you want to sort/group by and then sort/group by that formula?  If that works, it might be a way around the problem (although it'd still be nice to know why the field isn't working).

 James
0
 

Author Comment

by:wgcltd
Comment Utility

>  I suppose it's technically possible that it could construct a query that executed the stored procedure and filtered the results, but, AFAIK, it doesn't do anything like that.
>

I agree it's unlikely.

>  It's interesting that you see CALL in one place, but not the other, but I don't know what, if any, significance that has.  Also, you said that the one with CALL was the one that worked, but I did not see CALL in the report that I checked, so, if there was a difference, I would expect the one without CALL to be the one that worked.  <shrug>

Well it was definitely the one without the CALL that failed.

>
>  Forgive the obvious, but are you sure that you're running the same version of the same report in each location?  Seeing two different queries (one with CALL and one without) makes me wonder.

Yes it is the same version. But, my procedure is to work on the report on my local system using my local SP. Then I use "Update database" to change the report table to the SP on the remote server. I simply Update the current report table with the other SP. Then call Verify database and all is well. This is the step that seems to change the select statement in the report. But I assure you the SPs are identical.

>  In the Select Expert (where you create a record selection), the first thing in the list are the fields used in the report.  You're not looking at those instead of the fields in the procedure, are you?  

Yes, that is what I am looking at. I don't think it matters but I thought I would mention it.

> Forgetting the Select Expert for the moment, are the fields shown in the Field Explorer in the same order as the columns in the stored procedure? > AFAIK, they should be, although it's not something I normally pay that much attention to.

Yes, they *must* be. You can reorder them after connecting to a table, but if you connect to another table (as I am doing) the Verify will break. Kind of a bug in CR if you ask me.

>  Also, I suppose there's the general question of whether there's any chance that you're connecting to different db's, with different versions of the stored procedure.  You could try changing the stored procedure (eg. adding a new column) and seeing if the change shows up in the report in each location.

As I said, they are definitely the same version. Verify works fine.

>  To change the name shown in the report, go to Database > "Database Expert".  Click on the procedure name on the right side of the screen, then click again, or right-click and select Rename.  Then you can edit the name.  You're only changing the name shown in the report, not the actual datasource name.
>

Thanks! That worked. I did not know you could do that.

>
>  > If I sort or group by a formula field, the problem does not occur.
>
>  Hmm.  What if you create a formula that just contains the field that you want to sort/group by and then sort/group by that formula?  If that works, it might be a way around the problem (although it'd still be nice to know why the field isn't working).

It might be a work around but, as I said in my original post, I am afraid is will destroy all the other formulas which are dependent on the groups. CR has this wonderful way of wiping out all your work when you make significant core changes. Is there some kind of way to blanket change all the formulas that rely on the current groupings and make them switch. And will formulas that depend on groupings of formulas even work?

Thanks for the help.

0
 

Author Comment

by:wgcltd
Comment Utility
Oops, by "Update Database" I meant "Set Database Location"

0
 

Author Comment

by:wgcltd
Comment Utility
Also, it is the group summaries that are going to be lost by changing the groupby column to a formula. I'll try doing the copy/paste thing, onerous as it is, and see if that works.
0
 

Author Comment

by:wgcltd
Comment Utility
Well, I went ahead and bit the bullet and changed everything over to use formula field groupings. It took hours and it was a pain. I swear sometimes using CR is like stitching clothing with an ice pick. Anyway, it works now. Like I said in my initial post, I didn't want to use the formula hack, but it had to be that way. I figure it's just some weird bug in SQL Server or CR.

Thanks everyone for all your help.

0
 

Accepted Solution

by:
wgcltd earned 0 total points
Comment Utility
Unless anyone has anything else I'm gonna close this up. I'm a noob so I'm not sure about the points thing if no solution was given. Can somebody instruct me on what to do here?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> I simply Update the current report table with the other SP.
 > Then call Verify database and all is well. This is the step
 > that seems to change the select statement in the report.

 Interesting, although I don't know if it really tells us anything.


 > > ... the first thing in the list are the fields used in the
 > > report.  ...
 >
 > Yes, that is what I am looking at.

 Ah.  I'm pretty sure those are just in the order that the fields were added to the report, so if you pretty much went down the list of fields in the datasource and just put one thing after another in the report, they'd be in more or less the same order as the fields in the datasource.  But, beyond that, I don't think there's any connection to the order of the fields in the datasource.


 > > ... are the fields shown in the Field Explorer in the
 > > same order as the columns in the stored procedure?  ...
 >
 > Yes, they *must* be. You can reorder them after connecting
 > to a table, ...

 Really?  I've never tried it before, but I just did with a report that reads tables and one that uses a stored procedure.  In both cases, when I tried to drag a datasource field in the Field Explorer to somewhere else in the list, I got the "no" symbol (circle with a line through it) and it appeared to do nothing.
 Not that I _want_ to rearrange the fields in the datasource.  I'm just curious how you managed to do it.



 I didn't realize the "using a field in a formula" thing was what you were referring to in your original message, but I was mainly just wondering if it would work.  If all that you put in the formula was a field, then I really don't get why the formula would work, but not the field.  I've had problems where a stored procedure produced two columns with the same name and CR got "confused" about which one it should be using, or when I created two groups on the same field, but neither of those things applies to the report that you posted, so I really don't get it.

 Sorry we couldn't come up with a better way.  CR will propagate some changes through a report, but not changes to the groups.  It still _seems_ like the field should work.  Like you said, maybe it's some kind of bug somewhere.  Maybe it has something to do with the way you work on the report on your system and then change it to use the remote system, although if you use the same process with other reports and they work OK, that seems less likely.

 As for the points, if you're still wondering, I think you can ask to have them refunded.

 James
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now