using stored procedure as datasource for rpt

[jcaulder]

I am attempting to create a new Crystal 8.0 report using a SQL Server 2000 stored procedure as its datasource. The first step is to select the datasource when creating the report. The stored procedure shows in the list of datasources and I select it. However, once I select it, I am prompted to enter parameters. Regardless of what I enter for parameter values, when I click 'OK', nothing happens. I am still sitting in the select database window. It appears to completely ignore all parameter values I enter and to ignore the datasource(sp) I selected.

So my question is:

1) Do correct parameter values have to be entered prior to using a stored procedure as the datasource?

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.

3) If entering parameter values is not required, why can't I select this stored procedure as the datasource for the new report.

It seems strange to me that as I enter the parameter values and move to the next parameter value, the previously entered value seems to disappear. In other words, clicking back on a parameter I've already entered makes it appear nothing has been entered in it.

Any ideas as to what might be wrong? Query generated by the SP works perfectly when ran through Query Analyzer.

Thanks in advance!

 

[synapsevampire]

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.

Once you select a datasource, it returns you to the select database window, that is normal as often times people are selecting multiple data sources.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

jcaulder,

What you're experiencing is exactly what's supposed to happen.

When you are in the Data Explorer window, you select a stored procedure, enter the parameters, and get returned to the Data Explorer window. This doesn't mean nothing's happened. It means Crystal has accepted the datasource and has returned you back to the Data Explorer so that you can decide whether to add another datasource, or remove the datasource you just added. (You can verify this by checking the icon to the left of your datasource name. There should be a green checkmark over the icon where there wasn't one before.)

As for the disappearing parameter values, this is something that happens with Date and String parameters. If you return to a parameter you've already assigned a value to - but not committed - Crystal will assume that you didn't want the value you just gave it, and will overwrite it with the default (or null) value. (This behaviour isn't consistent with numerical parameters. Go figure.)

In short, add your procedure to the report. Don't go back to parameters you've already filled in, unless it's to correct an input. When you get returned to the Data Explorer, just hit 'Close'. You can see your procedure is your new datasource when the Visual Linking Expert opens up.

Naith

 

[jcaulder]

Unfortunately, it does not add the sp as the datasource. It appears to completely ignore the entire process. There is no check beside the selected datasource and there is no datasource added. Closing the datasource selection window after selecting the desired sp drops me back to the 'data' tab for the report wizard(which has no tables listed in available tables). I cannot go to 'Linked view' because no tables exist. I cannot add fields because no tables exist. Just as a note, if I add a regular table I have no problems. It is only related to stored procedures. It seems worth noting that the stored procedures I am trying to use already works with other reports that were created by the vendor. I am just trying to create a new report based on the existing sp. Once this is done, I planned to modify the sp to meet my needs. It must be something minor I'm doing wrong that is preventing the sp from being used as a datasource on these new reports. I can't find anything in the documentation that points to anything I'm doing wrong though.

Alas, the search goes on. Thanks for the replies!

 

[Naith]

Can you try a couple of tests?

Don't try adding the stored procedure through the Report Wizard. After hitting the New Report icon, choose Blank Report, and attempt to add the stored procedure.

Attempt to add a different stored procedure (once as another procedure owned by the same user, once with a different procedure preferably owned by your own user.)

Depending on the outcome of these tests, you could create a copy of one of your existing reports that already use this sproc, remove all the fields in the report, and start afresh with the report template that will already have the datasource assigned to it. That's a last resort that'll hopefully be unnecessary if we can get to the bottom of this.

Naith

 

[jcaulder]

I have previously tried not using the wizard but this resulted in the same problem.

All of the sp's are owned by dbo and I am logged in as 'sa' so I have full rights to them.

What I notice that is different about the sp's I create versus the existing ones from the vendor are that their sp's, when added to a new report already have default values for the parameteres entered and saved with the sp. When I try to enter values for the parameters in an sp I've modified or created, it appears to ignore them and certainly doesn't save them. Could this be the problem?

How do you force a stored procedure to save default values at the sp level in the database? I was able to use one of their unmodified sp's as a datasource for a new report without a problem. However, if I modify an sp or create a new one, I can no longer use it as a datasource.

It seems this entire problem revolves around parameter entry somehow.

Even your suggestion of copying an existing report and modifying to suit our needs won't work because it's the sp I need to modify as well as the report. As soon as I modify the sp, all of these problems begin to occur.

Where are default values for stored procedure parameters entered and how are they saved in SQL Server 2000? I thought the default values were created in the Crystal report but this can't be the only place since a new report with no parameters created yet still displays default values for the parameters.

 

[lyanch]

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.

Lisa

 

[synapsevampire]

Sounds buggy to me, it works fine here.

Post one of your SP's here, perhaps it's related to the code.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

I forgot to ask about the connectivity, are you using Native, ODBC, OLE DB?

I use the latter.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jcaulder]

Well, I have finally gotten the sp to be recognized as a datasource! I did this by removing all parameters from the sp and making them local variables with hardcoded values. While this obviously doesn't solve my problem since the report can't be written this way, at least it has isolated the problem to parameter passing. The parameters are all being declared as varchar but they are converted to various other types within the sp to be used in the 'where' clause(datetime, int, varchar). The SQL statement being built also uses 'in ()' so I'm thinking that depending upon the parameters passed, this logic is not working correctly. Once again, this was a vendor supplied sp and I have already found a bad join condition previously. I suspect the date conversion from varchar but I'll have to test to be sure. I'll just add them back one by one until it fails again.

Thanks for all the posts!! I would have probably given up by now if it weren't for the suggestions and assurances that it should be working just the way I was trying to do it! Thanks again!!

 

[synapsevampire]

Vewy, vewy stwange.

Had you not tested the SP?

You had posted:

" It seems worth noting that the stored procedures I am trying to use already works with other reports that were created by the vendor. I am just trying to create a new report based on the existing sp."

It makes no sense that it would work in some and not others...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jcaulder]

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!

 

[Naith]

You're supposed to cater for empty resultsets in your procedures.

e.g.:

If no_rows_returned

SELECT AllPKField
FROM YourTable
WHERE 1 = 2

which will return an empty row, instead of no rows. Crystal's only going to fall over if the columns you told it would be there, aren't, when you return no data.

Naith

 

[synapsevampire]

It would have been interesting to see the SQL. I'm guessing that what you were doing was returning a rowset if the @@rowcount > 0 (or some such), otherwise no select at all, which is a bad approach with SQL Programming in general as it places the ullness on all calling applications to code for it, minimizing reusability.

If the Table/View you're selecting from does not return any data in the SP (but does return an empty rowset), CR will still consider the SP valid.

As for it not returning an error message, that might be a problem with your configuration, CR does warn if no rowset is returned with the message:

"No Rowset was returned for this table, query or procedure"

Can't say why your CR doesn't warn you, perhaps an install problem, a connectivity problem, a bug, or perhaps if you turn on the File->Options->Reporting->More Report Engine Error Messages it will help (I always enable this).

Perhaps there are circunstances with certain connectivity or CR versions/patches where CR just hiccups when pointed at a datasource, but I've never seen it, so I have to disagree with everybody here, CR is supposed to warn, and more importantly, you should always return a rowset, unless your SP is targeted for a specific application where it makes sense to code otherwise.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jcaulder]

Both of these responses are very interesting. I checked the Crystal options and 'More Report Engine Errors' is enabled. As far as the sql being generated, the sp always executes a sql statement as its final step. There is no checking for a resultset in the sp. The final lines are

exec(@string1)
set nocount off
end

GO

and it is not enclosed in any conditional statements. The sql is a union of 5 different sql statements. 'Set nocount off' is simply telling sql server to report messages about rows affected right? It shouldn't interfere with CR in anyway should it?

I am very pleased to hear that CR should accept the datasource even if no records are returned. However, I can tell you that in this case, it does not. I am running CR 8.0.0.371. I don't mind posting the sp although it is quite long.

So, while I have it working and have completed the project I was working on, I'm still not sure what should be happening in CR. While I've never worked with a product that had problems with empty recordsets, it appears that both lyanch and naith think that CR has problems handling this case. I would certainly like to know what to expect(as I'm sure everyone else would).

It would seem very cumbersome to have to return a resultset to establish a datasource for a report so I'm leaning towards synapsevampire's assessment of the situation. However, I know I had to return a resultset to get the datasource to work. So, I think I'll create some very simple sp's and test the various scenarios. I'll report back on what I find. Once again, the sp is quite long and may exceed the allowable space for posting in here. If I start to find that other sp's work with CR without a resultset, I may attempt to post the offending sp for analysis. I'll provide the results shortly. I am fairly certain that with my install and configuration,

no recordset=no CR datasource and no warning

More to come shortly. . .

 

[synapsevampire]

Depending upon what you mean as a resultset...

If you return column headings and no data, CR should recognize it.

Since you're using dynamic SQL (which somewhat negates the performance advantages of an SP), perhaps nothing is generated in some instances.

Naith's post was curious to me because I think that he understands SQL well, but this:

If no_rows_returned

SELECT AllPKField
FROM YourTable
WHERE 1 = 2

makes no sense.

His select will also not return any rows...

Anyway, the point is, if you return nothing (not even a table definition), then CR should blow, that's not a dataset, if you return an empty rowset, CR is fine, otherwise you could never have a parameterized select which filtered out all rows.

I'm still guessing it was the SQL, but I'm not inclined to debug a large, dynamically constructed Union SP (using set nocount on/off should be fine).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

SynapseVampire,

The point of that SQL is that it returns no rows. I use that kind of thing in my stored procedures to force the procedures to return an empty row, as opposed to no rows.

This means that Crystal can handle the error handling, and more importantly, it won't fall over when you execute the report.

Naith

 

[jcaulder]

What you've said makes perfect sense to me(synapsevampire). I mean exactly what you mean by 'resultset'--> Columns are defined but not necessarily any rows returned. I use this interchangeably with 'rowset'. If the sp never created and executed a sql statement, then no resultset is created and obviously CR could not use that as a datasource. This was certainly not the case I was encountering. That is why I kept saying the 'query works' in Query Analyzer. By 'works', I meant creates column definitions(a necessity) but may or may not return rows(not a necessity).

The behavior you describe that CR should exhibit when attempting to accept a datasource is exactly what I would have expected.

So anyone reading this post will be thoroughly confused by this point I'm sure. However, most importantly, I'm no longer confused. Crystal will respond just as I expected it to as long as there aren't any problems with the sp.

As I mentioned previously, this was a vendor supplied sp and report that never worked correctly. Their join conditions resulted in a cross join in one of the five sql statements. This should have been a huge warning sign to not trust anything in the sp. It may come down to parameters being passed or the lack thereof that caused something to happen in the sp. The behavior still doesn't make sense to me though because pasting the output sql into Query Analyzer created an empty resultset(with column definitions). Since I now know this is all CR needs to create the datasource, why didn't it recognize it?

This was my first experience with SQL Server and Crystal. I have been working with Oracle, Infomaker and Access for the past 3 years.

I feel I have learned a wealth of information about SQL Server and Crystal by encountering this problem. I am greatly appreciative to all who responded and kept responding to my posts. When I feel I have found the cause of the problem, I will write back with the results.

Thanks!!