Problem with stored procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with stored procedure

When my page first loads, or hasn’t been loaded in a long time and someone goes to it, I am getting this 500-100 error "Operation is not allowed when the object is closed error". The weird thing is that when the page is refreshed there is no error and it works fine. I have a bug tracking system that has caught the details of this…http://bugs.rushfrisby.com/Bugs_get.asp?BugID=179 I believe it is a stored procedure problem, here is what I am using: CREATE PROCEDURE sp_Files_Fill_Top10_BySection (
@SectionID int=0,
@Groups varchar(255) = ”
) AS SET NOCOUNT ON — create temporary table
CREATE TABLE #TempTable
(FileID int, SectionID int, DateCreated datetime, Title varchar(255), FileName varchar(255), FileSize int, Comments varchar(8000)) DECLARE @GroupName varchar(255)
DECLARE group_cursor CURSOR FOR
SELECT * FROM split(@Groups, ‘,’)
OPEN group_cursor
FETCH NEXT FROM group_cursor INTO @GroupName
WHILE @@FETCH_STATUS = 0
BEGIN — fill temporary table
INSERT INTO #TempTable (FileID, SectionID, DateCreated, Title, FileName, FileSize, Comments)
(SELECT * FROM (SELECT TOP 100 PERCENT
dbo.Files.FileID,
dbo.Files.SectionID,
dbo.Files.DateCreated,
dbo.Files.Title,
dbo.Files.FileName,
dbo.Files.FileSize,
dbo.Files.Comments
FROM dbo.Files
LEFT OUTER JOIN dbo.FileManager ON dbo.Files.FileID = dbo.FileManager.FileID
LEFT OUTER JOIN dbo.UserGroups ON dbo.FileManager.GroupID = dbo.UserGroups.GroupID
WHERE dbo.UserGroups.GroupName = @GroupName
AND dbo.Files.SectionID = @SectionID
) DERIVEDTBL) FETCH NEXT FROM group_cursor INTO @GroupName
END
CLOSE group_cursor
DEALLOCATE group_cursor
— create temporary table
CREATE TABLE #TempTable2
(ID INT IDENTITY, FileID int, SectionID int, DateCreated datetime, Title varchar(255), FileName varchar(255), FileSize int, Comments varchar(8000)) INSERT INTO #TempTable2 (FileID, SectionID, DateCreated, Title, FileName, FileSize, Comments)
(SELECT DISTINCT * FROM #TempTable )
— return requested records
SELECT TOP 10 * FROM #TempTable2 ORDER BY DateCreated DESC
DROP TABLE #TempTable
DROP TABLE #TempTable2
SET NOCOUNT OFF
GO Here is the source code for the ASP object I am using: Public Function FillTop10(GroupList)
Cmd.ActiveConnection = Connection
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "sp_Files_Fill_Top10_BySection"
Cmd.Parameters.Append Cmd.CreateParameter("@SectionID", adInteger, adParamInput, 4, SectionID)
Cmd.Parameters.Append Cmd.CreateParameter("@Groups", adVarChar, adParamInput, 255, GroupList) Dim RecSet
Set RecSet = Server.CreateObject("ADODB.Recordset")
RecSet.CursorLocation = adUseClient
RecSet.CursorType = adOpenDynamic
RecSet.Open Cmd Count = RecSet.RecordCount
ReDim Files(Count) For i = 1 To Count
Set Files(i) = New clsFilesChild
With Files(i)
.FileID = RecSet("FileID")
.SectionID = RecSet("SectionID")
.DateCreated = RecSet("DateCreated")
.Title = RecSet("Title")
.FileName = RecSet("FileName")
.FileSize = RecSet("FileSize")
.Comments = RecSet("Comments")
End With
RecSet.MoveNext
Next
RecSet.Close
Set RecSet = Nothing
Call Cleanup
End Function
The error is on line "RecSet.Open Cmd" When the page refreshes this works, it just doesn’t work the first time. Seems really weird. If anyone knows what is going on please let me know! Thanks,
Rush
Hm, you say "when the ASP page refreshes this works", and the error itself is really about one of the objects in the ASP code that has been closed. So no reason to suspect that there’s something with the stored procedure. Is Cmd by any chance a global variable that may be reset by a time-out of your ASP page (or of the user session)? Don’t know too much about ASP, but I do hope you’re not working in an environment that lets you use variables without declaring them explicitly …
You might want to read up on SQL Injection. I could have a lot of fun with that query you just gave us. http://www.sqlteam.com/item.asp?ItemID=19595
http://www.databasejournal.com/features/mssql/article.php/3418281 MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I must add that procedure code is far from optimal. Let us know if you are interested in improving proc’s performance.
Sure any performance tips you can give me would be much appreciated. I have another proc I use to simulate recordset paging that I think could use some work too but I’ll post that in another topic. Rush
Here it goes:
-No need for select top 100 percent if order by is not used
-Condition on dbo.UserGroups.GroupName = @GroupName makes both outer joins useless
-No need for cursor
-No need for temp tables
-I believe we can eliminate even udf and that can help performance too but let’s first try this code:
CREATE PROCEDURE sp_Files_Fill_Top10_BySection (
@SectionID int=0,
@Groups varchar(255) = ”
) AS SET NOCOUNT ON select top 10 *
from (
SELECT distinct
dbo.Files.FileID,
dbo.Files.SectionID,
dbo.Files.DateCreated,
dbo.Files.Title,
dbo.Files.FileName,
dbo.Files.FileSize,
dbo.Files.Comments
FROM dbo.Files
JOIN dbo.FileManager ON dbo.Files.FileID = dbo.FileManager.FileID
join dbo.UserGroups ON dbo.FileManager.GroupID = dbo.UserGroups.GroupID
join dbo.split(@Groups, ‘,’) s on s.GroupName = dbo.UserGroups.GroupName
WHERE dbo.UserGroups.GroupName = @GroupName
AND dbo.Files.SectionID = @SectionID
) as t SET NOCOUNT OFF
You can also try:
CREATE PROCEDURE sp_Files_Fill_Top10_BySection (
@SectionID int=0,
@Groups varchar(255) = ”
) AS SET NOCOUNT ON select top 10
dbo.Files.FileID,
dbo.Files.SectionID,
dbo.Files.DateCreated,
dbo.Files.Title,
dbo.Files.FileName,
dbo.Files.FileSize,
dbo.Files.Comments
FROM dbo.Files
JOIN dbo.FileManager ON dbo.Files.FileID = dbo.FileManager.FileID
join dbo.UserGroups ON dbo.FileManager.GroupID = dbo.UserGroups.GroupID
join dbo.split(@Groups, ‘,’) s on s.GroupName = dbo.UserGroups.GroupName
WHERE dbo.UserGroups.GroupName = @GroupName
AND dbo.Files.SectionID = @SectionID) as t
Group by
dbo.Files.FileID,
dbo.Files.SectionID,
dbo.Files.DateCreated,
dbo.Files.Title,
dbo.Files.FileName,
dbo.Files.FileSize,
dbo.Files.Comments SET NOCOUNT OFF
]]>