SQL Server 7 Stored Procedure crashes IIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server 7 Stored Procedure crashes IIS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-25 : 14:46:00

Curt Morrison writes "My apologies if this doesn't qualify for your attention, but I would really appreciate it!

I'm running web services on a Win2k Advanced Server, SP2. I have SQL Server 7 (SP3) on a seperate server, with very good network speed (100 megabit). An .ASP page loads a query from a stored procedure (see below) from a table (tbl_web_reg_courses) with 900,000 records (primary key field is indexed). The page loads fine if there are fewer than 100,100 records in the tbl_web_reg_courses table, but once all of the records are added, the page fails to load and all of the web services on that server time-out (even when loaded directly on the server). The server must be rebooted to fix it. CPU and memory usage are normal, even after the page fails. I tried restarting EVERY service on the server too. I was able to recreate the same problem on a different win2k server as well. I'm almost convinced the problem has to do with either my stored procedure or the .ASP code.

Any suggestions would be greatly appreciated. I put the .ASP page and stored procedure below:

Here is the .ASP recordset:

Dim commViewCurricula__SYSTEMID
commViewCurricula__SYSTEMID = "0"
if(rsUserInfo.Fields.Item("SYSTEMID").Value <> "") then commViewCurricula__SYSTEMID = rsUserInfo.Fields.Item("SYSTEMID").Value

Dim commViewCurricula__MFG
commViewCurricula__MFG = "0"
if(Request.QueryString("MFG") <> "") then commViewCurricula__MFG = Request.QueryString("MFG")

Dim commViewCurricula__USERLOGONID
commViewCurricula__USERLOGONID = "xyz123"
if(Session("USERLOGONID") <> "") then commViewCurricula__USERLOGONID = Session("USERLOGONID")

set commViewCurricula = Server.CreateObject("ADODB.Command")
commViewCurricula.ActiveConnection = MM_WebAccounts_STRING
commViewCurricula.CommandText = "dbo.sp_webcourses_viewcurricula"
commViewCurricula.Parameters.Append commViewCurricula.CreateParameter("RETURN_VALUE", 3, 4)
commViewCurricula.Parameters.Append commViewCurricula.CreateParameter("@SYSTEMID", 3, 1,4,commViewCurricula__SYSTEMID)
commViewCurricula.Parameters.Append commViewCurricula.CreateParameter("@MFG", 3, 1,4,commViewCurricula__MFG)
commViewCurricula.Parameters.Append commViewCurricula.CreateParameter("@USERLOGONID", 200, 1,30,commViewCurricula__USERLOGONID)
commViewCurricula.CommandType = 4
commViewCurricula.CommandTimeout = 0
commViewCurricula.Prepared = true
set rsViewCurricula = commViewCurricula.Execute
rsViewCurricula_numRows = 0

Here is the SQL Server 7 Stored Procedure:

CREATE PROCEDURE sp_webcourses_viewcurricula
@SYSTEMID int,
@MFG int,
@USERLOGONID varchar (30)
AS
SELECT
dbo.tbl_web_course_description.CURRICULUMID,
dbo.tbl_web_course_curriculum.CURRICULUMNAME,
dbo.tbl_web_course_description.MFG,
dbo.tbl_web_accounts_menu_items_description.DESCRIPTION,
dbo.tbl_web_reg_courses.REGID
FROM
dbo.tbl_web_accounts_menu_items_description INNER JOIN
dbo.tbl_web_course_description ON
dbo.tbl_web_accounts_menu_items_description.MENUITEMID =
dbo.tbl_web_course_description.MENUITEMID INNER JOIN
dbo.tbl_web_course_curriculum ON
dbo.tbl_web_course_description.CURRICULUMID = dbo.tbl_web_course_curriculum.CURRICULUMID
LEFT OUTER JOIN
dbo.tbl_web_reg_courses ON
dbo.tbl_web_course_curriculum.SYSTEMID = dbo.tbl_web_reg_courses.SYSTEMID
AND
dbo.tbl_web_course_description.CURRICULUMID = dbo.tbl_web_reg_courses.CURRICULUMID
WHERE
(dbo.tbl_web_course_curriculum.SYSTEMID = @SYSTEMID)
AND
(dbo.tbl_web_course_description.MFG = @MFG)
OR
(dbo.tbl_web_reg_courses.USERLOGONID = @USERLOGONID AND dbo.tbl_web_course_curriculum.SYSTEMID = @SYSTEMID AND dbo.tbl_web_course_description.MFG = @MFG)
GROUP BY
dbo.tbl_web_course_description.MFG,
dbo.tb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources