Re: adp report does not recognize fields from stored procedure

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 Yuan wrote:
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

--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.access.queries)
  • Re: Self Joins and optimization
    ... EMPLOYEE# INT NOT NULL, ... CREATE TABLE TXREF ... INNER JOIN TX AS B ... While this passes the test data, it still needs a little work. ...
    (comp.databases.theory)
  • Need help on a SProc
    ... > @ID_QUESTIONNAIRE INT, ... >IDCYCLE int, ... >inner join t_familles as famille3 on ...
    (microsoft.public.sqlserver.server)