Solved

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

Posted on 2009-04-03
23
1,259 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
ID: 24058105
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
ID: 24058471
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
ID: 24058529
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
ID: 24059994
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
ID: 24062220
Are you grouping in the report or the stored procedure?

mlmcc
0
 

Author Comment

by:wgcltd
ID: 24065440
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
ID: 24065925
Is that in the designer?

mlmcc
0
 

Author Comment

by:wgcltd
ID: 24070686
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
ID: 24071104
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
ID: 24071204
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
ID: 24082999
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 100

Expert Comment

by:mlmcc
ID: 24083898
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
ID: 24084928
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
ID: 24085030
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
ID: 24085133
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
ID: 24085153
One more: If I sort or group by a formula field, the problem does not occur.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24094868
> 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
ID: 24104097

>  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
ID: 24104108
Oops, by "Update Database" I meant "Set Database Location"

0
 

Author Comment

by:wgcltd
ID: 24104187
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
ID: 24106806
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
ID: 24106820
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
ID: 24112835
> 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
CREATE DATABASE ENCRYPTION KEY 1 58
SSRS Access Remotely 5 55
SQL Server Communications Audit 5 70
sql 2012 cluster  SSRS cluster aware 2 26
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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

21 Experts available now in Live!

Get 1:1 Help Now