Crystal Reports "Failed to open a rowset"

daskas27
daskas27 used Ask the Experts™
on
Hello, I am getting an error when running a Crystal report. It first says “Failed to open a rowset”. I click “OK” I then get the following:
“Failed to open a rowset.”
“Details: S1000:[Microsoft][ODBC Visual FoxPro Driver]SQL:Statement too long.”

I know why I am getting the error, I just don’t know how to fix it. If it can be fixed. The problem is I want to report on a group of vendors. These vendors do not have any unique identifiers to group them by other than their ID so I have to list them out. Here is the gist of the report:

{tcspodsq.psopen} > 0.00 and
{tcspodet.postatus} <> "C" and
{tcspodet.popen} > 0.00 and
{tcspodsq.psdte} in {?FROM} to {?TO} and
{tcspodet.psuppcd} in ["ACCWEL", "ADVWEL", "AERSUP", "AERTES", "AGCINC", "AIRWEL", "AMKWEL", "ANOPLA", "BALCOM", "BENHEA", "BODBER", "BODWOR", "BOUMAC", "CAMTEC", "CINTHE", "COLCOA", "CONINS", "CONMET", "DAPRA", "DICTES", "DIPBRA", "DIRLAB", "EBTEC", "ELEMET", "ELLSUR", "EMCOR", "FLAPRE", "FOUPLA", "GENBUR", "HARCON", "HARLIN", "HAYHEA", "HITEMC", "HSEPF", "HYDHON", "HYGPRE", "INDINC", "INDSAW", "INTBEA", "INTFAC", "JETTEC", "KENEXT", "METALI", "METFIN", "METPRO", "METTES", "METWAK", "MOUBAS", "NECUST", "NEHTS", "NITCOR", "NYLWES", "OWHEA", "PARPRO", "PLAPLA", "POLFIN", "PRAAIR", "PRASUR", "PRAXAI", "PTIIND", "PURCOA", "QUAMEA", "RIVMAC", "RONMAR", "SIFAPP", "SPESTL", "SUPTHR", "THRROL", "UNAGRI", "UNICEN", "UNIGEA", "USCHRO", "USCHWI", "VALPLA", "WESELE", "WESPEN", "WETTOO", "ZZTOOL"]

If I remove some of the tcspodet.psuppcd IDs the report runs. Trouble is I need them all. Can this be remedied?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
You can add the list of vendors to a new table in the FoxPro database and join this table in your query. If you cannot change the FoxPro database you can add the list of vendors to an excel file and use a second connection joining the FoxPro data with Excel data. Joining data from 2 different connections will slow the report , but will work fine for small amount of data
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You could also try splitting the list in half

 {tcspodsq.psopen} > 0.00 and
 {tcspodet.postatus} <> "C" and
 {tcspodet.popen} > 0.00 and
 {tcspodsq.psdte} in {?FROM} to {?TO} and
(
 {tcspodet.psuppcd} in ["ACCWEL", "ADVWEL", "AERSUP", "AERTES", "AGCINC", "AIRWEL", "AMKWEL", "ANOPLA", "BALCOM", "BENHEA", "BODBER", "BODWOR", "BOUMAC", "CAMTEC", "CINTHE", "COLCOA", "CONINS", "CONMET", "DAPRA", "DICTES", "DIPBRA", "DIRLAB", "EBTEC", "ELEMET", "ELLSUR", "EMCOR", "FLAPRE", "FOUPLA", "GENBUR", "HARCON", "HARLIN", "HAYHEA", "HITEMC", "HSEPF", "HYDHON", "HYGPRE", "INDINC", "INDSAW", "INTBEA", "INTFAC", "JETTEC", "KENEXT"]
OR
{tcspodet.psuppcd} in [ "METALI", "METFIN", "METPRO", "METTES", "METWAK", "MOUBAS", "NECUST", "NEHTS", "NITCOR", "NYLWES", "OWHEA", "PARPRO", "PLAPLA", "POLFIN", "PRAAIR", "PRASUR", "PRAXAI", "PTIIND", "PURCOA", "QUAMEA", "RIVMAC", "RONMAR", "SIFAPP", "SPESTL", "SUPTHR", "THRROL", "UNAGRI", "UNICEN", "UNIGEA", "USCHRO", "USCHWI", "VALPLA", "WESELE", "WESPEN", "WETTOO", "ZZTOOL"]
)

Open in new window


mlmcc
I haven't used FoxPro, but if you can create something like a view or stored procedure in the db and use that as the datasource for your report, you could include the test on tcspodet.psuppcd in the view/SP.

 James
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

daskas27IT manager

Author

Commented:
Thank you folks for your efforts but nothing seems to resolve this issue. I have decided to close the question mostly because with Visual FoxPro no longer being supported there would be little interest in this topic.

Thanks again you Guys.
daskas27IT manager

Author

Commented:
I've requested that this question be deleted for the following reason:

No solution to this issue.
Top Expert 2011

Commented:
The SQL is created in a wrong way. Adding the values, which are currently hardcoded to a table and joining this table in the query will resolve the problem. If database cannot be changed the values could be imported from an Excel file.
daskas27IT manager

Author

Commented:
It is not possible for me to add tables.
Top Expert 2011

Commented:
You can add the values to an excel file and join the excel file with the data from foxpro in Crystal reports.
daskas27IT manager

Author

Commented:
Ok, I will try it. I have never attempted this before. Any tips? I will work on it tomorrow.
Top Expert 2011
Commented:
You already have the data from FoxPro. Create an excel file with psuppcd to exclude and add a new connection to this file in the report. Then lef join the table from FoxPro with the table from Excel. In records selection formula put a filter to get just records whith nulls in excel file ( no matching records in the excel file)
Top Expert 2011

Commented:
Accepting a solution with 0 point sure shows a great appreciation :)
Top Expert 2011

Commented:
Thanks Mr. Wolfe!
daskas27IT manager

Author

Commented:
Vasto, The option to award points was not available. Believe me, I appreciate your efforts.
Top Expert 2011

Commented:
Thanks daskas27!

Were you able to resolve the issue ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial