adp report does not recognize fields from stored procedure

[Peg via AccessMonster.com]

I am using AccessXP and SQLServer 2000.

I developed a stored procedure that sets derived values from input parameters,
and then uses those values to select data.

When I set the recordsource for an adp report, it sees the report but does
not interpret the fields. I get no field list, and when I try to run the
report it fails with a can't find data field in rowset message.

I can run the stored procedure from SQL Query Analyzer, and from the queries
section in the adp with no problem. The report just does not recognize it.

Any suggestions?

Thanks

 

[Norman Yuan]

Posting your SP may help other help you.

Just wild guess: add "SET NOCOUNT ON" at the beginning of your SP?

 

[mdullni1 via AccessMonster.com]

Here is my code. I have also tried putting the processing part of this code
in a multi-statement table UDF and selecting from that, but it didn't work
either. I have written procedures like this many times but have never
experienced this problem.

CREATE PROCEDURE dbo.rp_rptLckrAssgn(@plngAcadYrID int)
--*************************************************************************************************
-- Proc: rp_rptLckrAssgn
-- Purpose: Assign lockers to students for academic year and return an
exception
-- report of students who did not get assigned.
--*************************************************************************************************
AS

SET NOCOUNT ON

declare @lngClssOfHold int,@lngClssOfGrad int, @lngClssOfID1st int,
@lngClssOfID3rd int, @lngClssOfID4th int
declare @dtmMtrcDt datetime, @lngHumGen int, @lngBMEE int, @lngART int,
@lngCAMM int
declare @intFull int, @intSIR int, @intNotMtrc int, @intMDYr_4th int,
@intMDYr_3rd int, @intMDYr_1st int, @intReturn int
declare @lngLckrSzID_Fll int, @lngLckrSzID_Hlf int, @strMsgMD nvarchar(100),
@strMsgHumGen nvarchar(100), @strMsgGrad nvarchar(100)

set @lngLckrSzID_Fll = 1
set @lngLckrSzID_Hlf = 2
set @lngClssOfHold = 27
set @lngClssOfGrad = 6
set @lngClssOfID1st = dbo.intClssOfID_MDYr(@plngAcadYrID, 1)
set @lngClssOfID3rd = dbo.intClssOfID_MDYr(@plngAcadYrID, 3)
set @lngClssOfID4th = dbo.intClssOfID_MDYr(@plngAcadYrID, 4)
set @dtmMtrcDt = convert(datetime, '05/01/' + left(dbo.fstrAcadYrDsc
(@plngAcadYrID),4),101)
set @lngHumGen = 66
set @lngBMEE = 34
set @lngART = 44
set @lngCAMM = 35
set @intFull = 1
set @intSIR = 6
set @intNotMtrc = 7
set @intMDYr_4th = 4
set @intMDYr_3rd = 3
set @intMDYr_1st = 1
set @strMsgMD = ''
set @strMsgHumGen = ''
set @strMsgGrad = ''

/*
For simplification in my debugging, I commented out statements here that
performed inserts and updates, and altered the values of the empty string
variables above.
*/

select DgObj.lngDgObjID, @strMsgHumGen as strMsg,
dbo.strPrsnFullNm(Prsn.strPrsnFNm, Prsn.strPrsnLNm, Prsn.
strPrsnMNm, dbo.fstrSfxDsc(Prsn.lngSfxID),0,0,1) as strFullNm,
dbo.strClssOfDsc(DgObj.lngClssOfID) as strClssOfDsc,
Pgm.strStdPgmShrtDsc
from tblDgObjStts DgObj inner join
tblStd Std on Std.lngStdID = DgObj.lngStdID inner join
tblPrsn Prsn on Prsn.lngPrsnID = Std.lngPrsnID inner join
tblStdPgmDpt PgmDpt on PgmDpt.lngStdPgmDptID = DgObj.
lngStdPgmDptID inner join
tblStdPgm Pgm on Pgm.lngStdPgmID = PgmDpt.lngStdPgmID
where Std.ysnCrntStd = 1
and DgObj.lngTmSttsID in (1,6, 7)
and DgObj.lngClssOfID = 6
and PgmDpt.lngStdPgmID = 66
and Std.lngStdID not in (select lngStdID from vw_StdLckr_DgObjStts
where lngAcadYrID = @plngAcadYrID)
union

select DgObj.lngDgObjID, @strMsgGrad as strMsg,
dbo.strPrsnFullNm(Prsn.strPrsnFNm, Prsn.strPrsnLNm, Prsn.
strPrsnMNm, dbo.fstrSfxDsc(Prsn.lngSfxID),0,0,1) as strFullNm,
dbo.strClssOfDsc(DgObj.lngClssOfID) as strClssOfDsc,
Pgm.strStdPgmShrtDsc
from tblDgObjStts DgObj inner join
tblStd Std on Std.lngStdID = DgObj.lngStdID inner join
tblPrsn Prsn on Prsn.lngPrsnID = Std.lngPrsnID inner join
tblStdPgmDpt PgmDpt on PgmDpt.lngStdPgmDptID = DgObj.
lngStdPgmDptID inner join
tblStdPgm Pgm on Pgm.lngStdPgmID = PgmDpt.lngStdPgmID
where Std.ysnCrntStd = 1
and DgObj.lngTmSttsID in (1,6, 7)
and DgObj.lngClssOfID = 6
and Pgm.lngStdPgmID in (34, 44, 35)
and (DgObj.lngExpMtrcYrID = @plngAcadYrID or DATEDIFF(day,DgObj.
dtmAdmsDt, @dtmMtrcDt) = 0)
and Std.lngStdID not in (select lngStdID from vw_StdLckr_DgObjStts
where lngAcadYrID = @plngAcadYrID)
union

select DgObj.lngDgObjID, @strMsgMD as strMsg,
dbo.strPrsnFullNm(Prsn.strPrsnFNm, Prsn.strPrsnLNm, Prsn.
strPrsnMNm, dbo.fstrSfxDsc(Prsn.lngSfxID),0,0,1) as strFullNm,
dbo.strClssOfDsc(DgObj.lngClssOfID) as strClssOfDsc,
'M.D.'
from tblDgObjStts DgObj inner join
tblStd Std on Std.lngStdID = DgObj.lngStdID inner join
tblPrsn Prsn on Prsn.lngPrsnID = Std.lngPrsnID
where Std.ysnCrntStd = 1
and DgObj.lngTmSttsID in (1,6, 7)
and DgObj.lngClssOfID in (@lngClssOfID1st, @lngClssOfID3rd,
@lngClssOfID4th, @lngClssOfHold)
and Std.lngStdID not in (select lngStdID from vw_StdLckr_DgObjStts
where lngAcadYrID = @plngAcadYrID)

Posting your SP may help other help you.

Just wild guess: add "SET NOCOUNT ON" at the beginning of your SP?

I am using AccessXP and SQLServer 2000.

[quoted text clipped - 14 lines]

Thanks

 

[mdullni1 via AccessMonster.com]

Thanks for your reply. A co-worker found a silly mistake in my report design.
I left out the equal sign on a text field where I am using IIF. I still
don't get the field list, which I assume is because the SP is performing
multiple tasks, but my report runs now.

Thanks again.

Posting your SP may help other help you.

Just wild guess: add "SET NOCOUNT ON" at the beginning of your SP?

I am using AccessXP and SQLServer 2000.

[quoted text clipped - 14 lines]

Thanks