adp report does not recognize fields from stored procedure

  • Thread starter Thread starter Peg via AccessMonster.com
  • Start date Start date
P

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
 
Posting your SP may help other help you.

Just wild guess: add "SET NOCOUNT ON" at the beginning of your SP?
 
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)


Norman said:
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.
Click to expand...
[quoted text clipped - 14 lines]
Thanks
Click to expand...
Click to expand...
 
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.

Norman said:
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.
Click to expand...
[quoted text clipped - 14 lines]
Thanks
Click to expand...
Click to expand...
 
Back
Top