Well, if you go back to my original question and a previous one I posted on here, you'll see I kept asking about parameter values and whether they had to be correct for the datasource to be recognized. The reason it worked for the vendor's report is because their report had default values built into the report that always returned a recordset. My previous post had to do with the fact that I couldn't see modifications I made to the sp when adding a field. You(synapsevampire) made the suggestion of 'verifying database' which, along with the other steps I set forth in my previous post eventually got the new sp to work and see the new field in the report. However, that still doesn't work when connecting via ODBC but does work when using the native driver. Lyanch actually provided the answer when she asked if the query was returning a resultset. When I said the query worked perfectly in Query Analyzer, it did work perfectly although it didn't always return records. However, in Query Analyzer, even if a recordset isn't returned, you still get an empty recordset view with column definitions displayed. It would seem Crystal should be smart enough to extract the column names even if a resultset isn't returned. That or either warn the user that no recordset was returned and therefore the datasource cannot be used. To just do nothing over and over is quite uninformative and misleading. In a development environment with multiple database instances, default parameters that work on a LIVE datasource don't necessarily work on a test environment where data hasn't been entered lately. This is why I kept asking about parameters and kept saying that they were mixed types and questioning how and if they should be entered. You would think that entering no parameters would pull back a recordset since in essence it should be no 'where' clause. But because of the way the sp is written, leaving null for parameters returns no recordset. So, to answer the questions I asked from the beginning:
1) Do correct parameter values have to be entered prior to using a stored procedure as the datasource?
YES! Incorrect parameter values or types that cannot be converted correctly will cause the sp to return no records even though there may be no errors. Furthermore, Crystal will not warn of errors or an empty recordset; it just pretends you did nothing and leaves you in the 'select datasource' window.
2) If correct parameter values must be entered, what is the format for entering them through this crystal interface attempting to run the sp? I have a mixture of numeric, date, ranges, and text parameter datatypes.
Type parameter values in regular format and don't include any special characters to denote text, date, numeric, etc. However, if the sp is building a SQL statement that uses an 'IN' comparison, the parameter may have to be passed enclosed in parentheses(ie (1000,1001)).
3) If entering parameter values is not required, why can't I select this stored procedure as the datasource for the new report.
ENTERING PARAMETERS THAT RETURN A RESULTSET IS ABSOLUTELY REQUIRED. While this may seem obvious to experienced users, it seems a little misleading and is certainly not obvious to a new user especially since Crystal does not warn of this in any way. Running queries in any other environment(other than Crystal) shows the query column definitions just with an empty resultset. For someone used to working only on the query side and not a product such as Crystal, it is easy to think Crystal would be smart enough to know the data structure returned from a query and not force a recordset.
So in closing, lyanch hit the nail on the head when she said:
"Probably a silly question.. but the SP's do return a dataset don't they? If the SP doesn't return a dataset the viewer will ignore them as an invalid data source."
"IGNORE" cannot be emphasized enough. Synapsevampire, when you stated in your reply the following:
"Perhaps your SP isn't returning anything because of the way it's handling the parms, but it should either warn or add the SP to the list."
I took this as literal. That it should do one or the other. But it doesn't. If parameter values are bad or even if they're good but don't return any records, you get no warning and no SP is added to the list.
While I know this may seem common sense, I come from a background using Infomaker Reporting in which I wrote all queries at the report level. This is my first attempt at using a SP as a datasource and this certainly isn't obvious. It NEVER states this in any of 3 books I checked related to using sp's for datasources. I would think this should be emphasized since it causes this type of failure.
Thanks to everyone for replying. I am not saying anyone provided wrong information, just that lyanch responded with the exact, information I needed to hear. I am starting with literally zero Crystal experience so I needed the most basic answer that could be given. A query 'working' is not sufficient. You must know that a recordset will be returned in the database instance being queried(in a test or live environment, this can be quite different depending upon parameters). Hope this all makes sense.
Thanks again for all the posts!
