Results 1 to 2 of 2
Jan 19, 2006, 13:12 #1
- Join Date
- Oct 2002
- Lancashire, UK
- 0 Post(s)
- 0 Thread(s)
Can't get a stored procedure to return any rows...
I have a stored procedure in SQL Server 2000. It's as follows:Code:
CREATE PROCEDURE tuAbs @date VARCHAR(255), @tugroup VARCHAR(255) AS SELECT Student.Stu_Reference, Student.Stu_Lastname, Student.Stu_Firstname, DStumMark.dstum_mark, DStumMark.dstum_starttime, DStumMark.dstum_endtime, DStumMark.dstum_weekstartdate, Staff.Sta_Lastname, Staff.Sta_Firstname, Module.Mod_Reference, Module.Mod_Description, Student.Stu_Tutorgroupkey, DStumMark.dstum_latest FROM ((((Amr INNER JOIN (Staff INNER JOIN Asr ON Staff.Sta_Syskey = Asr.Asr_Staffkey) ON Amr.Amr_Actkey = Asr.Asr_Actkey) INNER JOIN Msr ON Amr.Amr_Modkey = Msr.Msr_Modkey) INNER JOIN Student ON Msr.Msr_Stukey = Student.Stu_Syskey) INNER JOIN DStumMark ON (Amr.Amr_Actkey = DStumMark.dstum_actkey) AND (Student.Stu_Syskey = DStumMark.dstum_stukey)) INNER JOIN Module ON Msr.Msr_Modkey = Module.Mod_Syskey WHERE (((DStumMark.dstum_mark)='o') AND ((DStumMark.dstum_weekstartdate)=@date) AND ((Student.Stu_Tutorgroupkey)=@tugroup) AND ((DStumMark.dstum_latest)=1)) ORDER BY Student.Stu_Lastname, Student.Stu_Firstname DESC; GO
When I try to run the procedure in an ASP (via an ODBC connection) it returns nothing! The query doesn't appear to fail, I get no error message (as I would if the query failed to run etc) it just returns no data. I have also tried putting the values into the procedure and running the query without sending any values to it - it still returns nothing.
I have subsituted the query in the ASP page with another procedure from the same database, which executes fine returning the data I expected.
I have been starting at this for a few hours now and I can't see why it runs in Query Analyzer but not in ASP
Any suggestions would be very much appreciated.
I've included the ASP code I'm attempting to test the query with below. The connection pc1 is establised via an include file and works fine for other queries run on the page.Code:
<ul> <% sql2 = " tuabs '1/8/2006', 'Y12DF-AHN-3' " set cat2 = pc1.Execute(sql2) do until cat2.eof response.write("<li>" & cat2.fields("stu_reference") & "</li>") cat2.movenext loop cat2.close %></ul>
Jan 19, 2006, 16:59 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 52 Post(s)
- 2 Thread(s)