Crystal Reports "Failed to open a rowset"
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
“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
vasto
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
You could also try splitting the list in half
mlmcc
{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"]
)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
James
ASKER
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.
Thanks again you Guys.
ASKER
I've requested that this question be deleted for the following reason:
No solution to this issue.
No solution to this issue.
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.
ASKER
It is not possible for me to add tables.
You can add the values to an excel file and join the excel file with the data from foxpro in Crystal reports.
ASKER
Ok, I will try it. I have never attempted this before. Any tips? I will work on it tomorrow.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Accepting a solution with 0 point sure shows a great appreciation :)
Thanks Mr. Wolfe!
ASKER
Vasto, The option to award points was not available. Believe me, I appreciate your efforts.
Thanks daskas27!
Were you able to resolve the issue ?
Were you able to resolve the issue ?