Is stored procedure same as pass-through query

[grnzbra]

I have a report that has a sub report in it. The report runs fine except the sub report doesn't show up at all. When the sub-report is run by itself. When I try to open the report I get a message that says:

"You can't use a pas-through or non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the ssubform or subreport to a crostab query , see the query's column headings property"

There about a dozen queries driving these reports. None of them are crosstab or pass-through queries. However, one of them has a stored procedure among the tables from which it is made. In another database, I have the same reports but the offending query only has two tables, and that report and subreport work fine together.

Does it sound like the stored procedure is the culprit?

 

[pbaldy]

I suppose the answer may vary based on environment, but in mine (A2k mdb front end/SQL Server 2k back end), they are not the same. The stored procedure is within SQL Server and can be accessed via a pass-through query in Access.

I typically execute stored procedures from ADO to add/edit/delete data, and use pass-through queries to return data from them for reports. I've got one report that uses a pass-through for the main report, and it has 4 sub-reports, each having its own pass-through as its source. Thus, the error message would seem to be misleading (no surprise from M$).

You didn't seem to finish the sentence about what happens when you open the sub-report on its own.

 

[grnzbra]

OOps. When I open the sub-report by itself, it' fine. The sub-report is not linked to the main report.

I just switched out the query for the old one which has only two tables and the report/sub-report combination works fine. The problem is that my job is to create queries so that those two table can be eliminated.

The SQL for the stored procedure (ORACLE) is:

select dl_id,
GetColtDetails.GetAdditionalBorrAllowed(dl_id) AdditionalBorrAllowed,
Getsecfacevalamt(dl_id) Secfacevalamt,
GetColtDetails.GetCurrColtAmtForDeal(dl_id) CurrColtAmt
from structured_facility

 

[grnzbra]

It appears that the sp is the culprit. In the query containing the sp, I set the field coming from the sp to 0 and removed the sp. Report/sub-report combination now working fine.