locked
negative return value causes error RRS feed

  • Question

  • Hi, 

    I have a SPROC that I wrote some time back.  The SPROC is called into my WCF service through a LINQ2SQL class.  There were no problems initially, but since adding fields (that are allowed to be Null), the LINQ2SQL classes fail when calling the SPROC.  Testing the SPROC in Enterprise manager, I can see it returns the correct output to indicate that a new record has been created, but sends a separate return value of '-6'.  Running other SPROCS which do inserts, they also return the output correctly, but sends a separate return value of '0'.  

     

    I'm guessing the return value of '-6' means something but searching doesnt bring any useful info.  Any ideas how to go about fixing this?

    thanks

    Tuesday, August 3, 2010 9:16 AM

Answers

  • I noted you have some code after the CATCH block that is not trapped in TRY...CATCH. You should add error handling for that code to catch and handle errors.

    Here are the system reserved stored procedure return error codes from some SQL Server old documentation:

    Value Description
    
    0 Procedure was executed successfully.
    -1 Object is missing.
    -2 Datatype error occurred.
    -3 Process was chosen as deadlock victim.
    -4 Permission error occurred.
    -5 Syntax error occurred.
    -6 Miscellaneous user error occurred.
    -7 Resource error, such as out of space, occurred.
    -8 Nonfatal internal problem was encountered.
    -9 System limit was reached.
    -10 Fatal internal inconsistency occurred.
    -11 Fatal internal inconsistency occurred.
    -12 Table or index is corrupt.
    -13 Database is corrupt.
    -14 Hardware error occurred.

     

     


    Plamen Ratchev
    • Proposed as answer by Naomi N Tuesday, August 3, 2010 9:40 PM
    • Marked as answer by obe wan kenobe Wednesday, August 4, 2010 10:50 AM
    Tuesday, August 3, 2010 8:37 PM
  • Thanks Plamen and Erland!

     

    Ok, I eventually found the cause of the error - was this line:

     SET @StartDate = dbo.DateTime(DATEPART(year,@StartDate),DATEPART(month,@StartDate),DATEPART(day,@StartDate),LEFT(@StartTime,2),RIGHT(@StartTime,2),0);

    It didnt quite like it. I ended up fixing the problem by creating a date string and then converting that to datetime which worked.

    I will take your advice and put a catch for the later bits of code also.

     

    Thanks.

    • Marked as answer by obe wan kenobe Wednesday, August 4, 2010 10:51 AM
    Wednesday, August 4, 2010 10:50 AM

All replies

  • Can you post the code for the Stored Procedure?

    Tom


    Tom Van Zele | Blog | Twitter
    Tuesday, August 3, 2010 9:24 AM
  • Plz check if there is somthing returning with RETURN or Selected with SELECT statement in your stored procedure.

    Tuesday, August 3, 2010 9:29 AM
  • code is below.  As I said in the initial post, the output value returns a valid ID and the row is entered in the DB when running via Enterprise Manager, however it does also return a separate return_value of -6.  But running via c# code in Linq2SQL class causes it to fail.
    -- Add the parameters for the stored procedure here
    	@CallID			bigint OUTPUT,
    	@CompanyId		int,
    	@Title			nvarchar(MAX),
    	@IssueTypeId	int,
    	@PriorityId		int = 3,
    	@OwnedById		int,
    	@OpenedById		int,
    	@ClientId		int
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    
    /*
    if IssueTypeId=0, PriorityId=0, OwnedById=0,OpenedById=0 and clientId=0
    this is a new call from email and thereferor rteat accordingly
    get default assignees fromt he db to add
    
    */
    	DECLARE
    		@OpenedDate DATETIME,
    		@StatusId	INT,
    		@TimeAdd	INT,
    		@StartTime	NVARCHAR(50),
    		@EndTime	NVARCHAR(50),
    		@Mon		BIT,
    		@Tue		BIT,
    		@Wed		BIT,
    		@Thurs		BIT,
    		@Fri		BIT,
    		@Sat		BIT,
    		@Sun		BIT,
    		@StartDate	DATETIME,
    		@DueDate	DATETIME;
    		
    		SET @StatusId = 0;
    		SELECT @OpenedDate = getdate();
    		SET @StartDate = @OpenedDate;
    		
    		BEGIN TRY
    		
    		SELECT @StartTime = SLADetails.StartTime,
    				@EndTime = SLADetails.EndTime,
    				@Mon = SLADetails.Monday,
    				@Tue = SLADetails.Tuesday,
    				@Wed = SLADetails.Wednesday,
    				@Thurs = SLADetails.Thursday,
    				@Fri = SLADetails.Friday,
    				@Sat = SLADetails.Saturday,
    				@Sun = SLADetails.Sunday
    		FROM Client INNER JOIN SLADetails 
    		ON Client.SLAId = SLADetails.SLAId
    		WHERE Client.ClientId=@ClientId;
    
    		
    		SET @TimeAdd =
    		CASE @PriorityId
    			WHEN 1 THEN
    				(SELECT SLADetails.CriticalHrs 
    				FROM Client INNER JOIN SLADetails 
    				ON Client.SLAId = SLADetails.SLAId
    				WHERE Client.ClientId=@ClientId)
    				
    			WHEN 2 THEN
    				(SELECT SLADetails.HighHrs
    				FROM Client INNER JOIN SLADetails
    				ON Client.SLAId = SLADetails.SLAId
    				WHERE Client.ClientId=@ClientId)
    				
    			WHEN 3 THEN
    				(SELECT SLADetails.MediumHrs
    				FROM Client INNER JOIN SLADetails
    				ON Client.SLAId = SLADetails.SLAId
    				WHERE Client.ClientId=@ClientId)
    				
    			WHEN 4 THEN
    				(SELECT SLADetails.LowHrs
    				FROM Client INNER JOIN SLADetails
    				ON Client.SLAId = SLADetails.SLAId
    				WHERE Client.ClientId=@ClientId)
    			END;
    
    		--Check if day covered
    		DECLARE @DayCovered		BIT,
    				@TimeCovered	BIT;
    			SET @DayCovered = 0;
    			SET @TimeCovered = 0;
    		
    		
    		--is current day covered? - if not set the start date to be next day and start time from SLA
    		WHILE @DayCovered=0
    		BEGIN
    		IF DAY(@StartDate)=1
    			BEGIN
    				IF @Sun=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    			END;
    			IF DAY(@StartDate)=2
    			BEGIN
    				IF @Mon=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    			END;
    			IF DAY(@StartDate)=3
    			BEGIN
    				IF @Tue=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    			END;
    			IF DAY(@StartDate)=4
    			BEGIN
    				IF @Wed=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    			END;
    			IF DAY(@StartDate)=5
    			BEGIN
    				IF @Thurs=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    			END;
    			IF DAY(@StartDate)=6
    			BEGIN
    				IF @Fri=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    			END;
    			IF DAY(@StartDate)=7
    			BEGIN
    				IF @Sat=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @StartDate = DATEADD(day,1,@StartDate);
    					
    			END;
    			
    			IF @DayCovered=0
    			BEGIN
    				--date has been changed
    				SET @StartDate = dbo.DateTime(DATEPART(year,@StartDate),DATEPART(month,@StartDate),DATEPART(day,@StartDate),LEFT(@StartTime,2),RIGHT(@StartTime,2),0);
    			END;			
    		END;
    		
    		
    		
    		
    		
    		--add hours to start time
    		SET @DueDate = DATEADD(hour, @TimeAdd, @StartDate);
    		
    		
    		DECLARE @CurrentEnd DATETIME;
    		
    		WHILE @TimeCovered = 0
    		BEGIN
    			--find max time for due date
    			SET @CurrentEnd = dbo.DateTime(DATEPART(year,@DueDate),DATEPART(month,@DueDate),DATEPART(day,@DueDate),LEFT(@EndTime,2), RIGHT(@EndTime,2),'0');
    			
    			IF @DueDate > @CurrentEnd
    			BEGIN
    				--gone over the time - need to see how much over we are.
    				DECLARE @OverDueHr INT,
    						@OverDueMin INT;
    						
    					SET @OverDueHr = DATEDIFF(hour,@DueDate,@CurrentEnd);
    					SET @OverDueMin = DATEDIFF(minute,@DueDate,DATEADD(hour,@OverDueHr,@CurrentEnd));
    					--add a day and then add the overdue hours and minutes
    					SET @DueDate = DATEADD(day,1,@DueDate);
    					SET @DueDate = dbo.DateTime(DATEPART(year,@DueDate),DATEPART(month,@DueDate),DATEPART(day,@DueDate),LEFT(@StartTime,2), RIGHT(@StartTime,2),'0');
    					SET @DueDate = DATEADD(hour,@OverDueHr,@DueDate);
    					SET @DueDate = DATEADD(minute, @OverDueMin, @DueDate);
    					SET @TimeCovered = 1;
    
    			END;
    			
    		END; --while
    		
    		--no final date needs to be checked again for day
    		SET @DayCovered = 0
    		
    		WHILE @DayCovered=0
    		BEGIN
    		IF DAY(@DueDate)=1
    			BEGIN
    				IF @Sun=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    			END;
    			IF DAY(@DueDate)=2
    			BEGIN
    				IF @Mon=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    			END;
    			IF DAY(@DueDate)=3
    			BEGIN
    				IF @Tue=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    			END;
    			IF DAY(@DueDate)=4
    			BEGIN
    				IF @Wed=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    			END;
    			IF DAY(@DueDate)=5
    			BEGIN
    				IF @Thurs=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    			END;
    			IF DAY(@DueDate)=6
    			BEGIN
    				IF @Fri=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    			END;
    			IF DAY(@DueDate)=7
    			BEGIN
    				IF @Sat=1
    				BEGIN
    					--covered
    					SET @DayCovered =1;
    				END
    				ELSE
    					SET @DueDate = DATEADD(day,1,@DueDate);
    					
    			END;
    					
    		END;
    		
    	
    		
    		
    		END TRY
    		BEGIN CATCH
    		
    			--if cant get SLAdetails then just add a standrad 3 day lead time
    			SET @DueDate = DATEADD(hour,72,@StartDate);
    		
    		END CATCH	
    		
    		IF @DueDate = null
    		BEGIN
    		--one final check
    			SET @DueDate = DATEADD(hour,72,@OpenedDate);
    		END;
    		
    		SELECT @CallId = (SELECT MAX(CallId) FROM dbo.Call WHERE CompanyId=@CompanyId)+1;
    		
    		INSERT INTO Call
    		(CallId,CompanyId,Title,IssueTypeId,StatusId,PriorityId,OwnedById,OpenedById,DueDate,ClientId,OpenedDate)
    		VALUES
    		(@CallId,@CompanyId,@Title,@IssueTypeId,@StatusId,@PriorityId,@OwnedById,@OpenedById,@DueDate,@ClientId,@OpenedDate)
    		
    		--insert into call users current user
    		
    		INSERT INTO Assignee
    			(CallId,UserId,CompanyId)
    			VALUES
    			(@CallId,@OpenedById,@CompanyId);
    		
    		IF @OwnedById <> @OpenedById
    		BEGIN
    			INSERT INTO Assignee
    			(CallId,UserId,CompanyId)
    			VALUES
    			(@CallId,@OwnedById,@CompanyId);
    		END;
    		
    		--insert template fields into call_Details
    		INSERT INTO CallDetails(CallId,TemplateId,CompanyId,FieldId) 
    		(SELECT (SELECT @CallId as CallId),TemplateId,CompanyId,FieldId from TemplateDetails where CompanyId=@CompanyId);
    		
    		
    		
    	--	SELECT @CallId
    
    END

    Tuesday, August 3, 2010 10:20 AM
  • The return value indicates that an error occurs when you are running the procedure. You have a CATCH block which drops all errors on the floor - or so you though, but SQL Server is leaking the news.

    First of all, I think you should review why that error occurs. There are situations where you may prefer to swallow errors in a CATCH handler, but I get a little nervous when this happens with a so long TRY block.

    If you want to avoid the -6 to be returned, you need an explicit "RETURN 0".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, August 3, 2010 8:05 PM
  • I noted you have some code after the CATCH block that is not trapped in TRY...CATCH. You should add error handling for that code to catch and handle errors.

    Here are the system reserved stored procedure return error codes from some SQL Server old documentation:

    Value Description
    
    0 Procedure was executed successfully.
    -1 Object is missing.
    -2 Datatype error occurred.
    -3 Process was chosen as deadlock victim.
    -4 Permission error occurred.
    -5 Syntax error occurred.
    -6 Miscellaneous user error occurred.
    -7 Resource error, such as out of space, occurred.
    -8 Nonfatal internal problem was encountered.
    -9 System limit was reached.
    -10 Fatal internal inconsistency occurred.
    -11 Fatal internal inconsistency occurred.
    -12 Table or index is corrupt.
    -13 Database is corrupt.
    -14 Hardware error occurred.

     

     


    Plamen Ratchev
    • Proposed as answer by Naomi N Tuesday, August 3, 2010 9:40 PM
    • Marked as answer by obe wan kenobe Wednesday, August 4, 2010 10:50 AM
    Tuesday, August 3, 2010 8:37 PM
  • > Here are the system reserved stored procedure return error codes from some SQL Server old documentation:

    I was about to say that these numbers are no longer relevant, but looking closer at the list, I realise that it does describe the current cicrumstances in a way.

    That is, the return value is 10 minus the severity level of the error. So an error with severity level 14 yields return status -4. And severity level 14 is indeed permission errors.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, August 4, 2010 7:42 AM
  • Thanks Plamen and Erland!

     

    Ok, I eventually found the cause of the error - was this line:

     SET @StartDate = dbo.DateTime(DATEPART(year,@StartDate),DATEPART(month,@StartDate),DATEPART(day,@StartDate),LEFT(@StartTime,2),RIGHT(@StartTime,2),0);

    It didnt quite like it. I ended up fixing the problem by creating a date string and then converting that to datetime which worked.

    I will take your advice and put a catch for the later bits of code also.

     

    Thanks.

    • Marked as answer by obe wan kenobe Wednesday, August 4, 2010 10:51 AM
    Wednesday, August 4, 2010 10:50 AM