Stored Procedure
-
Hi
I wrote a stored procedure which inserts multiple rows into a database. After writing the stored procedure the application which is using this has been damaged. I am giving the stored procedure. Anyone please tell me the modification for that stored procedure.
CREATE PROCEDURE ReserveMultipleSelectedComputers(@ServiceTagIDs varchar(1000),@UserID int,@Duration int,@DueDate DateTime,@Comments varchar(100),@Count int=0 output)
AS
DECLARE @ServID varchar(10),@Pos int;
DECLARE @ReserveStatus int;
DECLARE @AvailableComputerCount int;
DECLARE @Service_Tag_Id varchar(50);
SET @ServiceTagIDs = LTRIM(RTRIM(@ServiceTagIDs))+','
SET @Pos=CHARINDEX(',',@ServiceTagIDs,1)
IF REPLACE(@ServiceTagIDs, ',', '') <>''
BEGIN
BEGIN TRANSACTION
WHILE @Pos > 0
BEGIN
PRINT @pos
SET @ServID = LTRIM(RTRIM(LEFT(@ServiceTagIDs,@Pos-1)))
If @ServID <> ''
BEGIN
SELECT @AvailableComputerCount = Count(*) FROM Mst_ProjwiseComputers PC, Status S WHERE PC.Service_Tag_id = @ServID AND PC.Status_Id = S.Status_Id AND S.Status_Name = 'Available';
IF @AvailableComputerCount=1
BEGIN
SELECT @ReserveStatus=(SELECT Top 1 Status_Id FROM Status WHERE Status_Name='Reserved')
UPDATE Mst_ProjwiseComputers SET Status_Id=@ReserveStatus WHERE Service_Tag_id=@ServID
IF @Duration > 0
INSERT INTO Transactions(Service_tag_Id,User_Id,Start_Time,End_Time,Comments,Status_Id)VALUES(@ServID,@UserID,getDate(),DATEADD(hour,@Duration,getDate()),@Comments,@ReserveStatus)
ELSE
INSERT INTO Transactions(Service_tag_Id,User_Id,Start_Time,End_Time,Comments,Status_Id)VALUES(@ServID,@UserID,getDate(),@DueDate,@Comments,@ReserveStatus)
END
ELSE
BEGIN
Goto ERR_HANDLER
END
SET @ServiceTagIDs = RIGHT(@ServiceTagIDs,LEN(@ServiceTagIDs)-@Pos)
SET @Pos = CHARINDEX(',',@ServiceTagIDs,1)
END
END
SET @Count = 1
COMMIT TRANSACTION
END
ERR_HANDLER:
IF(@@ERROR <> 0)
BEGIN
SET @Count=0
RollBack Transaction
END
SELECT @Count
GO
This is the stored procedure. This is executing in the sql query analyzer. But when I call this procedure from the application it is giving an error called Server Application Unavailable. Only the page using this procedure is not working. The rest of the application is working. I will be thankful for the solution provided.
Regards
Mahathi.
-
If I understand you correctly (and I can't believe I do), aren't you telling us that the procedure is working fine, but also asking for a modification to it?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan
The error cam when I execute the procedure is
Event ID: 1001 Source: ASP.NET 1.1.4322.0 aspnet_wp.exe (PID:3152) was recycled because memory consumption exceeded the 608MB(60 percent of available RAM).
So to make that procedure work within the constraint I want to make the stored procedure in a more efficient way. So if it is possible give me more efficient stored procedure.
Thanks in advance.
-
If calling this stored procedure from query analyser is working fine for you, but not from your asp.net - you probably need to post your problem to an asp.net forum.
We can improve your stored procedure for you, although I'm not sure that's the cause of your error. If you'd like it improving regardless, post your table ddl, some sample data and the result you would like.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Mahathi,
just a few links that may shed some light on your asp/iis issue
problem - http://support.microsoft.com/default.aspx?scid=kb;en-us;324772
idleTimeout solution - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/gngrfprocessmodelsection.asp
hope this helps!
Daniel
MISfIT
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply