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
LVL 1
daskas27IT managerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
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 ConsultantCommented:
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
James0628Commented:
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
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

daskas27IT managerAuthor 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 managerAuthor Commented:
I've requested that this question be deleted for the following reason:

No solution to this issue.
vastoCommented:
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 managerAuthor Commented:
It is not possible for me to add tables.
vastoCommented:
You can add the values to an excel file and join the excel file with the data from foxpro in Crystal reports.
daskas27IT managerAuthor Commented:
Ok, I will try it. I have never attempted this before. Any tips? I will work on it tomorrow.
vastoCommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vastoCommented:
Accepting a solution with 0 point sure shows a great appreciation :)
vastoCommented:
Thanks Mr. Wolfe!
daskas27IT managerAuthor Commented:
Vasto, The option to award points was not available. Believe me, I appreciate your efforts.
vastoCommented:
Thanks daskas27!

Were you able to resolve the issue ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.