Crystal Reports rowset error during remote OLE DB connection to SQL Server
Posted on 2009-04-03
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.