Solved

UPDATE statement in MS SQL Server 2008 stored procedure returns -6 error sometimes

Posted on 2012-04-04
12
673 Views
Last Modified: 2012-04-09
I am troubleshooting an in-house program at work that uses MS SQL.  I don't have much MS SQL experience starting out.

The in-house program is used by many workstations at once to enter in data.  When the data is entered into the program, a stored procedure is called to update the tables with the appropriate data.  The -6 error does not happen all of the time, however, through some troubleshooting, I've managed to narrow the problem down to certain UPDATE statements in the stored procedure (not necessarily the same one each time).  The UPDATE statements won't update, and the error message I see returned is "-6".


First question:  What is this "-6" supposed to mean exactly?

Second question:  Is there anything in the following code that would give away what is happening?  Since I'm not too familiar with MS SQL TRANSACTIONs, I'm thinking the problem might have something to do with that.


Stored Procedure:
USE [FS]
GO
/****** Object:  StoredProcedure [dbo].[UpdateShipmentFromReceiving]    Script Date: 04/04/2012 14:18:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UpdateShipmentFromReceiving]
      @ChgDesc    varchar(20),
      @ChgMachine varchar(20),
      @ChgInit    char(4),
      @Msg       varchar(4000) output,
      @RecordType          char(1),
    @WRNum               int output,
    @QuoteNum            int output,
    @HawbNo              varchar(12) output,
    @DimsParams          varchar(max),
    @TrackingNumbers     varchar(max),
    @NewUPSShipperCode   char(6),
    @CustLastShipFlags   char(3),
    @ShipperNum          int,
    @ConsigneeNum        int,
    @AgentNum            int,
    @SoldToNum           int,
    @BillShipNum         int,
    @BillToNum           int,
    @POD                 char(3),
    @POE                 char(3),
    @POD1                char(3),
    @POD2                char(3),
    @POD3                char(3),
    @FirstPOD            char(3),
    @Carrier1            char(2),
    @Carrier2            char(2),
    @Carrier3            char(2),
    @Flight1             char(7),
    @Flight2             char(7),
    @Description         varchar(30),
    @ShipperRef          varchar(25),
    @ConsPo              varchar(25),
    @Place               varchar(20),
    @Currency            char(3),
    @RA                  char(1),
    @PaymentType         char(1),
    @Licensed            char(1),
    @LicenseNo           varchar(15),
    @Pier                varchar(20),
    @VesselName          varchar(30),
    @Mode                char(1),
    @Trucker             varchar(15),
    @PrintedWR           bit,
    @PrintedBL           bit,
    @AWBType             char(1),
    @CheckNum            char(10),
    @Insure              char(1),
    @MiscInit            char(3),
    @ReceivingInit       char(3),
    @Docs                char(1),
    @IFreightChkNo       char(10),
    @AdvanceChkNo        char(10),
    @Security            char(1),
    @CustomsVal          float,
    @InsuredVal          float,
    @EstimatedValue      float,
    @NoPcs               int,
    @Weight              float,
    @DimWeight           float,
    @ValuationChg        float,
    @Ifchg               float,
    @CodAmt              float,
    @Totfee              float,
    @AdvChg              float,
    @AwbFee              float,
    @CodFee              float,
    @CollectFee          float,
    @EEIFee              float,
    @IfFee               float,
    @InsFee              float,
    @AdvFee              float,
    @Ongofee             float,
    @HazMat              float,
    @OthChg1             float,
    @OthChg2             float,
    @OthChg3             float,
    @OthName1            varchar(15),
    @OthName2            varchar(15),
    @OthName3            varchar(15),
    @OthType1            char(1),
    @OthType2            char(1),
    @OthType3            char(1),
    @Lk_Awbfee           bit,
    @Lk_Codfee           bit,
    @Lk_CollectFee       bit,
    @Lk_EEIfee           bit,
    @Lk_Iffee            bit,
    @Lk_Insfee           bit,
    @Lk_Advfee           bit,
    @Lk_Ongofee          bit,
    @Lk_Hazmat           bit,
    @Lk_InsuredVal       bit,
    @Lk_Rate             bit,
    @Lk_FuelSCAmt        bit,
    @DueCarrier          float,
    @DueAgent            float,
    @PrepaidAmt          float,
    @CollectAmt          float,
    @FuelSCAmt           float,
    @MfuelSCRate         float,
    @ItemNo              char(4),
    @RateDesc1           varchar(20),
    @RateDesc2           varchar(20),
    @Basis               char(2),
    @Rate                float,
    @Charge              float,
    @Chgwt               float,
    @Cft                 float,
    @Final               char(1),
    @BookDTime           datetime,
    @DelDTime            datetime,
    @ExecDTime           datetime,
    @EtaDTime            datetime,
    @Openbox             char(1),
    @HazMatType          char(2),
    @BookShipDate        datetime,
    @BookPickup          char(1),
    @BookBy              char(3),
    @MakePDF             char(1),
    @Make_Inv            char(1),
    @TrackMode           char(1),
    @CarrierVal          float,
    @PaymentMethod       char(3),
    @WmBasis             char(2),
    @Company             char(1),
    @AKAcontact          varchar(50),
    @WRScreenType        int,
    @AWB_Notes           varchar(max)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      
      --Error Flag
      declare @eflag varchar(max)

    -- Insert statements for procedure here
    set @Msg = 'OK'
      begin try
      
     BEGIN Transaction
     declare @onhand int
     
     if @WRnum<=0 begin
       set @onhand = @NoPcs    
       EXEC GetNextDocNum 'W',@WRNum output
           insert INTO Shipment (Shipment.WRNum) VALUES (@WrNum)
            
           IF @AwbType = 'Q' begin
         EXEC GetNextDocNum 'Q',@QuoteNum output
         end
       end
      else begin
        declare @loadnum int, @splitShipped bit
        select @loadnum=Loadnum, @splitShipped=SplitShipped, @onhand=onhand
           from shipment where wrnum=@wrnum
        if @LoadNum is null  or @loadnum = 0 begin
          set @onhand = @NoPcs    
          end
     
        end  
       
      SET @eflag = 'after get wrnum'
       
      if @HAWBNO is null begin
        set @HAWBNO = ''
       end
      if @HAWBNO = '' begin
        set @HAWBNO = rtrim(cast(@wrnum as char(8)))
        WHILE  len(@HawbNo)  <8 BEGIN
            set @HawbNo = '0'+@HawbNo
         end
        set @Hawbno='000-'+@HawbNo
        end
     
      SET @eflag = 'after get HAWBNO'
     
      update Shipment
        set ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, ChgTime=CURRENT_TIMESTAMP,
            ShipperNum          = @ShipperNum,
            ConsigneeNum        = @ConsigneeNum,
            AgentNum            = @AgentNum,
            SoldToNum           = @SoldToNum,
            BillShipNum         = @BillShipNum,
            BillToNum           = @BillToNum,
            HawbNo              = @HawbNo,
            POD                 = @POD,
            POE                 = @POE,
            POD1                = @POD1,
            POD2                = @POD2,
            POD3                = @POD3,
            FirstPOD            = @FirstPOD,
            Carrier1            = @Carrier1,
            Carrier2            = @Carrier2,
            Carrier3            = @Carrier3,
            Flight1             = @Flight1,
            Flight2             = @Flight2,
            Description         = @Description,
            ShipperRef          = @ShipperRef,
            ConsPo              = @ConsPo,
            Place               = @Place,
            Currency            = @Currency,
            RA                  = @RA,
            PaymentType         = @PaymentType,
            Licensed            = @Licensed,
            LicenseNo           = @LicenseNo,
            Pier                = @Pier,
            VesselName          = @VesselName,
            Mode                = @Mode,
            Trucker             = @Trucker,
            PrintedWR           = @PrintedWR,
            PrintedBL           = @PrintedBL,
            AWBType             = @AWBType,
            CheckNum            = @CheckNum,
            Insure              = @Insure,
            MiscInit            = @MiscInit,
            ReceivingInit       = @ReceivingInit,
            Docs                = @Docs,
            IFreightChkNo       = @IFreightChkNo,
            AdvanceChkNo        = @AdvanceChkNo,
            Security            = @Security,
            CustomsVal          = @CustomsVal,
            InsuredVal          = @InsuredVal,
            EstimatedValue      = @EstimatedValue,
            NoPcs               = @NoPcs,
            Onhand              = @Onhand,
            Weight              = @Weight,
            DimWeight           = @DimWeight,
            ValuationChg        = @ValuationChg,
            Ifchg               = @Ifchg,
            CodAmt              = @CodAmt,
            Totfee              = @Totfee,
            AdvChg              = @AdvChg,
            AwbFee              = @AwbFee,
            CodFee              = @CodFee,
            CollectFee          = @CollectFee,
            EEIFee              = @EEIFee,
            IfFee               = @IfFee,
            InsFee              = @InsFee,
            AdvFee              = @AdvFee,
            Ongofee             = @Ongofee,
            HazMat              = @HazMat,
            OthChg1             = @OthChg1,
            OthChg2             = @OthChg2,
            OthChg3             = @OthChg3,
            OthName1            = @OthName1,
            OthName2            = @OthName2,
            OthName3            = @OthName3,
            OthType1            = @OthType1,
            OthType2            = @OthType2,
            OthType3            = @OthType3,
            Lk_Awbfee           = @Lk_Awbfee,
            Lk_Codfee           = @Lk_Codfee,
            Lk_CollectFee       = @Lk_CollectFee,
            Lk_EEIfee           = @Lk_EEIfee,
            Lk_Iffee            = @Lk_Iffee,
            Lk_Insfee           = @Lk_Insfee,
            Lk_Advfee           = @Lk_Advfee,
            Lk_Ongofee          = @Lk_Ongofee,
            Lk_Hazmat           = @Lk_Hazmat,
            Lk_InsuredVal       = @Lk_InsuredVal,
            Lk_Rate             = @Lk_Rate,
            Lk_FuelSCAmt        = @Lk_Fuelscamt,
            DueCarrier          = @DueCarrier,
            DueAgent            = @DueAgent,
            PrepaidAmt          = @PrepaidAmt,
            CollectAmt          = @CollectAmt,
            FuelSCAmt           = @FuelSCAmt,
            MfuelSCRate         = @MfuelSCRate,
            ItemNo              = @ItemNo,
            RateDesc1           = @RateDesc1,
            RateDesc2           = @RateDesc2,
            Basis               = @Basis,
            Rate                = @Rate,
            Charge              = @Charge,
            Chgwt               = @Chgwt,
            Cft                 = @Cft,
            Final               = @Final,
            BookDTime           = @BookDTime,
            DelDTime            = @DelDTime,
            ExecDTime           = @ExecDTime,
            EtaDTime            = @EtaDTime,
            Openbox             = @Openbox,
            HazMatType          = @HazMatType,
            BookShipDate        = @BookShipDate,
            BookPickup          = @BookPickup,
            BookBy              = @BookBy,
            CarrierVal          = @CarrierVal,
            MakePDF             = @MakePDF,
            Make_Inv            = @Make_Inv,
            TrackMode           = @TrackMode,
            PaymentMethod       = @PaymentMethod,
            WmBasis             = @WmBasis,
            Company             = @Company,
            AKAcontact          = @AKAcontact,
            QuoteNum            = @QuoteNum,
            WRScreenType        = @WRScreenType,
            AWB_Notes           = @AWB_Notes
       
           
        where wrnum=@wrnum
       
        SET @eflag = 'after update shipment table'
       
   if len(@DimsParams) > 10 begin
     exec UpdateDims @wrnum,@DimsParams
   end    
   
   SET @eflag = 'after update dims'
   
   if len(@TrackingNumbers) > 10 begin
--     exec UpdateDims @wrnum,@DimsParams
     exec UpdateTrackingNumbers @wrnum,@TrackingNumbers  
   end    
   
   SET @eflag = 'after update tracking numbers'

   if len(@NewUPSShipperCode)>0 BEGIN
     update Customer set UPSCUST=@NewUPSShipperCode WHERE CustNum=@ShipperNum
   end

      SET @eflag = 'after update UPS shipper code'

  if Substring(@CustLastShipFlags,1,1) ='Y' BEGIN
    update Customer SET  ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, -- ChgTime=CURRENT_TIMESTAMP,
       LASTSHIP4 = LASTSHIP3,
       LASTSHIP3 = LASTSHIP2,
       LASTSHIP2 = LASTSHIPDT,
       LASTSHIPDT = @DelDtime
    where CustNum=@ShipperNum
    end

  SET @eflag = 'after update LastShipFlags1'

  if Substring(@CustLastShipFlags,2,1) ='Y' BEGIN
    update Customer SET  ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, -- ChgTime=CURRENT_TIMESTAMP,
       LASTSHIP4 = LASTSHIP3,
       LASTSHIP3 = LASTSHIP2,
       LASTSHIP2 = LASTSHIPDT,
       LASTSHIPDT = @DelDtime
    where CustNum=@ConsigneeNum
    end

  SET @eflag = 'after update LastShipFlags2'

  if Substring(@CustLastShipFlags,3,1) ='Y' BEGIN
    update Customer SET  ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, -- ChgTime=CURRENT_TIMESTAMP,
       LASTSHIP4 = LASTSHIP3,
       LASTSHIP3 = LASTSHIP2,
       LASTSHIP2 = LASTSHIPDT,
       LASTSHIPDT = @DelDtime
    where CustNum=@AgentNum
    end

   SET @eflag = 'after update LastShipFlags3'
   
   exec CreateShipmentPieces @wrnum,@NoPcs
   
   SET @eflag = 'after execute CreateShipmentPieces'
   
   DECLARE @Consolidation bit
   
   SELECT @Consolidation=Consolidation  from shipment where wrnum=@wrnum
   
   IF @Consolidation=1 BEGIN
      update Shipment
        set ChgDesc = 'RCV_Consol', ChgMachine=@ChgMachine, ChgInit=@ChgInit, ChgTime=CURRENT_TIMESTAMP,
            POD  = @POD,
            POD1 = @POD1,
            POD2 = @POD2,
            POD3 = @POD3
        where ConsWrnum=@WrNum
   end
   
   SET @eflag = 'after sonsolidate wrnum'
   
-- xxx
      -- Do UpdateCustShipINfo() logic (Shipper, COnsignee, Agent)
      -- SEt xecution date time if needed
      commit transaction
    end try
    begin catch
      set @Msg = @eflag + ': ' + dbo.GetErrorMessage()
     
--      set @Message = @sMsg
      rollback transaction
    end catch  

END



I should add that the it seems to give this -6 error on the UPDATE SHIPMENT and UPDATE CUSTOMER statements...
0
Comment
Question by:MiamiTec
  • 7
  • 5
12 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 37807969
First, I doubt the -6 error is caused by transactions.  However, it is possible that the UPDATEs do not update because an error transfers control to the catch block and that rolls back the transaction.  

The only way that proc can pass a value back to the caller is by an  output parameter (of which your proc has several).  My attention goes to @msg which gets loaded in the catch block by a call to the function dbo.GetErrorMessage.    I would look into that function as a source of the -6 message.  

Also, look at the calling code and "walk back" to follow how the -6 message is conveyed from the proc.  That will help diagnose the cause of the -6.
0
 

Author Comment

by:MiamiTec
ID: 37808092
First, I doubt the -6 error is caused by transactions.  However, it is possible that the UPDATEs do not update because an error transfers control to the catch block and that rolls back the transaction.

Control is being passed to the catch block for sure, but it is getting there because of the UPDATE calls.

The only way that proc can pass a value back to the caller is by an  output parameter (of which your proc has several).  My attention goes to @msg which gets loaded in the catch block by a call to the function dbo.GetErrorMessage.    I would look into that function as a source of the -6 message.

Here is the code for dbo.GetErrorMessage:

USE [FS]
GO
/****** Object:  UserDefinedFunction [dbo].[GetErrorMessage]    Script Date: 04/04/2012 15:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[GetErrorMessage]
(
	-- Add the parameters for the function here
)
RETURNS varchar(4000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @sMsg varchar(4000)

	-- Add the T-SQL statements to compute the return value here
--	set  @sMsg = 'Randy'
	
	  set @sMsg = @sMsg + '*Error*' + CHAR(13)+CHAR(10)+ '  Procedure: ' + ERROR_PROCEDURE() + CHAR(13)+CHAR(10)
      set @sMsg = @sMsg + '  Line:' + cast(ERROR_LINE() as varchar(10)) + CHAR(13)+CHAR(10)
      set @sMsg = @sMsg + '  Error: ' + ERROR_MESSAGE() 

	-- Return the result of the function
	if @sMsg is NULL
		set @sMsg = 'Error is NULL'	
	
	RETURN @sMsg
END

Open in new window


Every time there is a -6 error, 'Error is NULL' is returned.

Also, look at the calling code and "walk back" to follow how the -6 message is conveyed from the proc.  That will help diagnose the cause of the -6.

I can't really "walk back" any further for now because a program I am not privy to (a DELPHI program I think) is calling the stored procedure.

Now that I stop and think about it, could the -6 error be a DELPHI return value?  It's not something I see in any MS SQL troubleshooting directly, but something that pops up through an error screen made by the programmer.  I was told it was an SQL error and I guess I took it at face value.

EDIT: DELPHI PASCAL (??) to be more specific
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37808459
OK, the [dbo].[GetErrorMessage] procedure is concealing your error.  Try this fix:


ALTER FUNCTION [dbo].[GetErrorMessage]
(
	-- Add the parameters for the function here
)
RETURNS varchar(4000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @sMsg varchar(4000)

	-- Add the T-SQL statements to compute the return value here
--	set  @sMsg = 'Randy'
 	  set @Msg = ''                   
	  set @sMsg = @sMsg + '*Error*' + CHAR(13)+CHAR(10)+ '  Procedure: ' + ERROR_PROCEDURE() + CHAR(13)+CHAR(10)
      set @sMsg = @sMsg + '  Line:' + cast(ERROR_LINE() as varchar(10)) + CHAR(13)+CHAR(10)
      set @sMsg = @sMsg + '  Error: ' + ERROR_MESSAGE() 

	-- Return the result of the function
	if @sMsg is NULL
		set @sMsg = 'Error is NULL'	
	
	RETURN @sMsg
END

Open in new window

0
 

Author Comment

by:MiamiTec
ID: 37810669
OK, thank you!  I'm going to apply the fix and see what comes back in the error messages.

Since the only difference I can see is:

set @sMsg = ''

Open in new window


is the thinking here that since @sMsg is Null it is not adding the data from the other "set @sMsg" lines so we must initialize to a non-null value first?
0
 

Author Comment

by:MiamiTec
ID: 37811626
I received an error this morning, but all that returned for @sMsg was an empty string.

it seems like the following code:

set @sMsg = @sMsg + '*Error*' + CHAR(13)+CHAR(10)+ '  Procedure: ' + ERROR_PROCEDURE() + CHAR(13)+CHAR(10)
set @sMsg = @sMsg + '  Line:' + cast(ERROR_LINE() as varchar(10)) + CHAR(13)+CHAR(10)
set @sMsg = @sMsg + '  Error: ' + ERROR_MESSAGE()

Open in new window


is not setting the variable the way I expect.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37812418
Is the thinking here that since @sMsg is Null it is not adding the data from the other "set @sMsg" lines so we must initialize to a non-null value first?

Yes.  But note the TSQL I provided has a typo:  "@msg" instead of "@smsg"

There is a setting:  CONCAT_NULL_YIELDS_NULL  which by default is ON

For testing purposes, you might try
SET  CONCAT_NULL_YIELDS_NULL OFF

at the top of the function (But I would not leave it that way because you don't know what else it affects)


it seems like the following code:
...                                        
is not setting the variable the way I expect.
It does seem that way.  But that sequence of statements will either return NULL (which subsequenty gets coverted to 'Error is NULL') or a bunch of literals and codes.  I don't see anyway it can return an empty string.  

The only scenario that comes to mind is that something higher in your call stack is having difficulty with the complex error message that contains carriage returns and the like.  Again for sake of testing, perhaps temporarily simplify the error message to this:


ALTER FUNCTION [dbo].[GetErrorMessage]
(
	-- Add the parameters for the function here
)
RETURNS varchar(4000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @sMsg varchar(4000)                  
	 set @sMsg = isnull(ERROR_MESSAGE(),  'Error is NULL'	)
	RETURN @sMsg
END

Open in new window



Also, for sake of good style and logic (but I don't think it fixes the problem) change the order of statements in your catch block to this:

begin catch
      rollback transaction
      set @Msg = @eflag + ': ' + dbo.GetErrorMessage()
--     set @Message = @sMsg
    end catch  

Open in new window


The point being, you don't really want to rollback the error handler function, so call that after you rollback the transaction.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:MiamiTec
ID: 37812632
For testing purposes, you might try
SET  CONCAT_NULL_YIELDS_NULL OFF

at the top of the function (But I would not leave it that way because you don't know what else it affects)

I put this statement in the stored procedure catch block along with the order change you suggested:

begin catch
      rollback transaction
      SET CONCAT_NULL_YIELDS_NULL OFF
      set @Msg = @eflag + ': ' + dbo.GetErrorMessage()
      SET CONCAT_NULL_YIELDS_NULL ON
--      set @Message = @sMsg
      --rollback transaction
    end catch

Open in new window


I could not put the SET CONCAT_NULL_YIELDS_NULL OFF statement directly into the function dbo.GetErrorMessage.  When I tried I got this error:

Invalid use of a side-effecting operator 'SET OPTION OFF' within a function.

Which apparently means that I cannot use that statement within a function.

The change I made did return something besides an empty string though!  Hopefully, I'll get a message with useful error information the next time one happens, but I'm guessing I'll just get back my static strings (Procedure:, Line:, Error:,)

In the meantime, my next suspect is the ''GetNextDocNum" stored procedure that is called from the "UpdateShipmentFromReceiving" stored procedure that we have been talking about.  I suspect that it is giving out the same document numbers to different shipments, but I'm not quite sure how it is happening.  Here is the code:

USE [FS]
GO
/****** Object:  StoredProcedure [dbo].[GetNextDocNum]    Script Date: 04/05/2012 12:45:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetNextDocNum]
	-- Add the parameters for the stored procedure here
	--@DocType char(2),
	@DocType	varchar(2),
	@NewNum		integer output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	set @NewNum = 0
	Begin Transaction
	if @DocType = 'W' begin
	  update DocNumbers set @NewNum=Wrnum, WRNum=@NewNum+1 where ID=1
	end
	commit Transaction
END

Open in new window


EDIT: Took out some commented code
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 37812903
Yikes!  A couple red flags are waving with respect to that procedure.

First, when @DocType = 'Q', it returns 0 unconditionally.  There is no point in calling it to get 0 back every time.

Second (and this well may be the source of your problem), your update statement is not logically tolerant of concurrent updates.  Please change it  to this:
 update DocNumbers set @NewNum=Wrnum, WRNum=WRNum+1 where ID=1

Open in new window


Third, I'm not too pleased to see the Begin/Commit Transaction inside your nested procedure.  That gives you a nested transaction which, at best, doesn't commit the docnumber update as intended and, at worst, will cause problems for the outer transaction.  While I don't think it solves the problem at hand, I suggest removing those two statements and allowing the outer procedure to control the transaction.
0
 

Author Comment

by:MiamiTec
ID: 37813890
Thank you so much for the suggestions!  I think that may have fixed the problem, but I will not be sure until either tomorrow or sometime next week.  I will post again on it, though!
0
 

Author Comment

by:MiamiTec
ID: 37816251
I caught a couple errors that actually returned some information!

They are:

Error: :   Procedure: UpdateShipmentFromReceiving  Line:154
  Error: Lock request time out period exceeded.
Procedure Name: UpdateShipmentFromReceiving

and

Error: after get HAWBNO:   Procedure: UpdateShipmentFromReceiving  Line:185
  Error: Lock request time out period exceeded.
Procedure Name: UpdateShipmentFromReceiving

Here is the full UpdateShipmentFromReceiving code again for reference:

USE [FS]
GO
/****** Object:  StoredProcedure [dbo].[UpdateShipmentFromReceiving]    Script Date: 04/05/2012 09:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UpdateShipmentFromReceiving] 
	@ChgDesc    varchar(20),
	@ChgMachine varchar(20),
	@ChgInit    char(4),
	@Msg       varchar(4000) output,
	@RecordType          char(1),
    @WRNum               int output,
    @QuoteNum            int output,
    @HawbNo              varchar(12) output,
    @DimsParams          varchar(max), 
    @TrackingNumbers     varchar(max), 
    @NewUPSShipperCode   char(6), 
    @CustLastShipFlags   char(3),
    @ShipperNum          int,
    @ConsigneeNum        int,
    @AgentNum            int,
    @SoldToNum           int,
    @BillShipNum         int,
    @BillToNum           int,
    @POD                 char(3),
    @POE                 char(3),
    @POD1                char(3),
    @POD2                char(3),
    @POD3                char(3),
    @FirstPOD            char(3),
    @Carrier1            char(2),
    @Carrier2            char(2),
    @Carrier3            char(2),
    @Flight1             char(7),
    @Flight2             char(7),
    @Description         varchar(30),
    @ShipperRef          varchar(25),
    @ConsPo              varchar(25),
    @Place               varchar(20),
    @Currency            char(3),
    @RA                  char(1),
    @PaymentType         char(1),
    @Licensed            char(1),
    @LicenseNo           varchar(15),
    @Pier                varchar(20),
    @VesselName          varchar(30),
    @Mode                char(1),
    @Trucker             varchar(15),
    @PrintedWR           bit,
    @PrintedBL           bit,
    @AWBType             char(1),
    @CheckNum            char(10),
    @Insure              char(1),
    @MiscInit            char(3),
    @ReceivingInit       char(3),
    @Docs                char(1),
    @IFreightChkNo       char(10),
    @AdvanceChkNo        char(10),
    @Security            char(1),
    @CustomsVal          float,
    @InsuredVal          float,
    @EstimatedValue      float,
    @NoPcs               int,
    @Weight              float,
    @DimWeight           float,
    @ValuationChg        float,
    @Ifchg               float,
    @CodAmt              float,
    @Totfee              float,
    @AdvChg              float,
    @AwbFee              float,
    @CodFee              float,
    @CollectFee          float,
    @EEIFee              float,
    @IfFee               float,
    @InsFee              float,
    @AdvFee              float,
    @Ongofee             float,
    @HazMat              float,
    @OthChg1             float,
    @OthChg2             float,
    @OthChg3             float,
    @OthName1            varchar(15),
    @OthName2            varchar(15),
    @OthName3            varchar(15),
    @OthType1            char(1),
    @OthType2            char(1),
    @OthType3            char(1),
    @Lk_Awbfee           bit,
    @Lk_Codfee           bit,
    @Lk_CollectFee       bit,
    @Lk_EEIfee           bit,
    @Lk_Iffee            bit,
    @Lk_Insfee           bit,
    @Lk_Advfee           bit,
    @Lk_Ongofee          bit,
    @Lk_Hazmat           bit,
    @Lk_InsuredVal       bit,
    @Lk_Rate             bit,
    @Lk_FuelSCAmt        bit,
    @DueCarrier          float,
    @DueAgent            float,
    @PrepaidAmt          float,
    @CollectAmt          float,
    @FuelSCAmt           float,
    @MfuelSCRate         float,
    @ItemNo              char(4),
    @RateDesc1           varchar(20),
    @RateDesc2           varchar(20),
    @Basis               char(2),
    @Rate                float,
    @Charge              float,
    @Chgwt               float,
    @Cft                 float,
    @Final               char(1),
    @BookDTime           datetime,
    @DelDTime            datetime,
    @ExecDTime           datetime,
    @EtaDTime            datetime,
    @Openbox             char(1),
    @HazMatType          char(2),
    @BookShipDate        datetime,
    @BookPickup          char(1),
    @BookBy              char(3),
    @MakePDF             char(1),
    @Make_Inv            char(1),
    @TrackMode           char(1),
    @CarrierVal          float,
    @PaymentMethod       char(3),
    @WmBasis             char(2),
    @Company             char(1),
    @AKAcontact          varchar(50),
    @WRScreenType        int,
    @AWB_Notes           varchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--Error Flag
	declare @eflag varchar(max)

    -- Insert statements for procedure here
    set @Msg = 'OK'
	begin try
	
     BEGIN Transaction
     declare @onhand int
      
     if @WRnum<=0 begin
       set @onhand = @NoPcs     
       EXEC GetNextDocNum 'W',@WRNum output
	     insert INTO Shipment (Shipment.WRNum) VALUES (@WrNum) 
	      
	     IF @AwbType = 'Q' begin
         EXEC GetNextDocNum 'Q',@QuoteNum output
         end
       end
      else begin
        declare @loadnum int, @splitShipped bit
        select @loadnum=Loadnum, @splitShipped=SplitShipped, @onhand=onhand
           from shipment where wrnum=@wrnum
        if @LoadNum is null  or @loadnum = 0 begin
          set @onhand = @NoPcs     
          end
      
        end  
       
      SET @eflag = 'after get wrnum' 
       
      if @HAWBNO is null begin
        set @HAWBNO = ''
       end 
      if @HAWBNO = '' begin
        set @HAWBNO = rtrim(cast(@wrnum as char(8)))
        WHILE  len(@HawbNo)  <8 BEGIN
            set @HawbNo = '0'+@HawbNo
         end
        set @Hawbno='000-'+@HawbNo
        end
      
      SET @eflag = 'after get HAWBNO'
      
      update Shipment
        set ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, ChgTime=CURRENT_TIMESTAMP,
            ShipperNum          = @ShipperNum,
            ConsigneeNum        = @ConsigneeNum,
            AgentNum            = @AgentNum,
            SoldToNum           = @SoldToNum,
            BillShipNum         = @BillShipNum,
            BillToNum           = @BillToNum,
            HawbNo              = @HawbNo,
            POD                 = @POD,
            POE                 = @POE,
            POD1                = @POD1,
            POD2                = @POD2,
            POD3                = @POD3,
            FirstPOD            = @FirstPOD,
            Carrier1            = @Carrier1,
            Carrier2            = @Carrier2,
            Carrier3            = @Carrier3,
            Flight1             = @Flight1,
            Flight2             = @Flight2,
            Description         = @Description,
            ShipperRef          = @ShipperRef,
            ConsPo              = @ConsPo,
            Place               = @Place,
            Currency            = @Currency,
            RA                  = @RA,
            PaymentType         = @PaymentType,
            Licensed            = @Licensed,
            LicenseNo           = @LicenseNo,
            Pier                = @Pier,
            VesselName          = @VesselName,
            Mode                = @Mode,
            Trucker             = @Trucker,
            PrintedWR           = @PrintedWR,
            PrintedBL           = @PrintedBL,
            AWBType             = @AWBType,
            CheckNum            = @CheckNum,
            Insure              = @Insure,
            MiscInit            = @MiscInit,
            ReceivingInit       = @ReceivingInit,
            Docs                = @Docs,
            IFreightChkNo       = @IFreightChkNo,
            AdvanceChkNo        = @AdvanceChkNo,
            Security            = @Security,
            CustomsVal          = @CustomsVal,
            InsuredVal          = @InsuredVal,
            EstimatedValue      = @EstimatedValue,
            NoPcs               = @NoPcs,
            Onhand              = @Onhand,
            Weight              = @Weight,
            DimWeight           = @DimWeight,
            ValuationChg        = @ValuationChg,
            Ifchg               = @Ifchg,
            CodAmt              = @CodAmt,
            Totfee              = @Totfee,
            AdvChg              = @AdvChg,
            AwbFee              = @AwbFee,
            CodFee              = @CodFee,
            CollectFee          = @CollectFee,
            EEIFee              = @EEIFee,
            IfFee               = @IfFee,
            InsFee              = @InsFee,
            AdvFee              = @AdvFee,
            Ongofee             = @Ongofee,
            HazMat              = @HazMat,
            OthChg1             = @OthChg1,
            OthChg2             = @OthChg2,
            OthChg3             = @OthChg3,
            OthName1            = @OthName1,
            OthName2            = @OthName2,
            OthName3            = @OthName3,
            OthType1            = @OthType1,
            OthType2            = @OthType2,
            OthType3            = @OthType3,
            Lk_Awbfee           = @Lk_Awbfee,
            Lk_Codfee           = @Lk_Codfee,
            Lk_CollectFee       = @Lk_CollectFee,
            Lk_EEIfee           = @Lk_EEIfee,
            Lk_Iffee            = @Lk_Iffee,
            Lk_Insfee           = @Lk_Insfee,
            Lk_Advfee           = @Lk_Advfee,
            Lk_Ongofee          = @Lk_Ongofee,
            Lk_Hazmat           = @Lk_Hazmat,
            Lk_InsuredVal       = @Lk_InsuredVal,
            Lk_Rate             = @Lk_Rate,
            Lk_FuelSCAmt        = @Lk_Fuelscamt,
            DueCarrier          = @DueCarrier,
            DueAgent            = @DueAgent,
            PrepaidAmt          = @PrepaidAmt,
            CollectAmt          = @CollectAmt,
            FuelSCAmt           = @FuelSCAmt,
            MfuelSCRate         = @MfuelSCRate,
            ItemNo              = @ItemNo,
            RateDesc1           = @RateDesc1,
            RateDesc2           = @RateDesc2,
            Basis               = @Basis,
            Rate                = @Rate,
            Charge              = @Charge,
            Chgwt               = @Chgwt,
            Cft                 = @Cft,
            Final               = @Final,
            BookDTime           = @BookDTime,
            DelDTime            = @DelDTime,
            ExecDTime           = @ExecDTime,
            EtaDTime            = @EtaDTime,
            Openbox             = @Openbox,
            HazMatType          = @HazMatType,
            BookShipDate        = @BookShipDate,
            BookPickup          = @BookPickup,
            BookBy              = @BookBy,
            CarrierVal          = @CarrierVal,
            MakePDF             = @MakePDF,
            Make_Inv            = @Make_Inv,
            TrackMode           = @TrackMode,
            PaymentMethod       = @PaymentMethod,
            WmBasis             = @WmBasis,
            Company             = @Company,
            AKAcontact          = @AKAcontact,
            QuoteNum            = @QuoteNum,
            WRScreenType        = @WRScreenType,
            AWB_Notes           = @AWB_Notes
        
            
        where wrnum=@wrnum
        
        SET @eflag = 'after update shipment table'
        
   if len(@DimsParams) > 10 begin
     exec UpdateDims @wrnum,@DimsParams
   end     
   
   SET @eflag = 'after update dims'
   
   if len(@TrackingNumbers) > 10 begin
--     exec UpdateDims @wrnum,@DimsParams
     exec UpdateTrackingNumbers @wrnum,@TrackingNumbers   
   end     
   
   SET @eflag = 'after update tracking numbers'

   if len(@NewUPSShipperCode)>0 BEGIN
     update Customer set UPSCUST=@NewUPSShipperCode WHERE CustNum=@ShipperNum 
   end

	SET @eflag = 'after update UPS shipper code'

  if Substring(@CustLastShipFlags,1,1) ='Y' BEGIN
    update Customer SET  ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, -- ChgTime=CURRENT_TIMESTAMP,
       LASTSHIP4 = LASTSHIP3,
       LASTSHIP3 = LASTSHIP2,
       LASTSHIP2 = LASTSHIPDT,
       LASTSHIPDT = @DelDtime
    where CustNum=@ShipperNum
    end 

  SET @eflag = 'after update LastShipFlags1'

  if Substring(@CustLastShipFlags,2,1) ='Y' BEGIN
    update Customer SET  ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, -- ChgTime=CURRENT_TIMESTAMP,
       LASTSHIP4 = LASTSHIP3,
       LASTSHIP3 = LASTSHIP2,
       LASTSHIP2 = LASTSHIPDT,
       LASTSHIPDT = @DelDtime
    where CustNum=@ConsigneeNum
    end 

  SET @eflag = 'after update LastShipFlags2'

  if Substring(@CustLastShipFlags,3,1) ='Y' BEGIN
    update Customer SET  ChgDesc = @ChgDesc, ChgMachine=@ChgMachine, ChgInit=@ChgInit, -- ChgTime=CURRENT_TIMESTAMP,
       LASTSHIP4 = LASTSHIP3,
       LASTSHIP3 = LASTSHIP2,
       LASTSHIP2 = LASTSHIPDT,
       LASTSHIPDT = @DelDtime
    where CustNum=@AgentNum
    end 

   SET @eflag = 'after update LastShipFlags3'
   
   exec CreateShipmentPieces @wrnum,@NoPcs
   
   SET @eflag = 'after execute CreateShipmentPieces'
   
   DECLARE @Consolidation bit
   
   SELECT @Consolidation=Consolidation  from shipment where wrnum=@wrnum
   
   IF @Consolidation=1 BEGIN
      update Shipment
        set ChgDesc = 'RCV_Consol', ChgMachine=@ChgMachine, ChgInit=@ChgInit, ChgTime=CURRENT_TIMESTAMP,
            POD  = @POD,
            POD1 = @POD1,
            POD2 = @POD2,
            POD3 = @POD3
        where ConsWrnum=@WrNum
   end
   
   SET @eflag = 'after sonsolidate wrnum'
   
-- xxx
      -- Do UpdateCustShipINfo() logic (Shipper, COnsignee, Agent)
      -- SEt xecution date time if needed
      commit transaction
    end try
    begin catch
      rollback transaction
      SET CONCAT_NULL_YIELDS_NULL OFF
      set @Msg = @eflag + ': ' + dbo.GetErrorMessage()
      SET CONCAT_NULL_YIELDS_NULL ON
--      set @Message = @sMsg
      --rollback transaction
    end catch  

END

Open in new window


I have seen this "Lock request time out period exceeded" error happen before, but I guess I really don't know what it means.  I have SET LOCK TIMEOUT = -1 milliseconds, which (I thought) means that there is no time-out period (it will wait forever).

Should I change the SET LOCK TIMEOUT to a value besides -1?
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 37817262
The good news is that you are now getting meaningful error messages.  So there's hope.   In fairness, I believe we've gotten to the bottom of the -6 message and that it's reasonable to close out this question and move on.

The bad news is that you are facing concurrency control and transaction management issues.  The implications are pervasive and they are best addressed architecturally in the foundation of your application. They tend to be intermittent and fairly difficult to isolate/resolve after-the-fact.  In a sense, I'm afraid you've discovered a "can of worms".   Short of retrofitting your architecture from top-to-bottom, you are likely to be chasing case-by-case errors for awhile.   I hope that gives you some idea why I'm encouraging you to phrase followup questions that are more to the point.

However, I will offer you a brief overview of what you are up against.  First, it's important to understand what Lock Timeout means.  The database uses LOCKS to  protect it's resources from corruption and integrity degradation.  You may be inclined to hate locks because they interfere with the flow of your application, but they are really a developers very good friend.  Without them, you would be trashing your data instead of getting errors.  

Simplistically, LOCKS mark a database resource as busy and block other processes from accessing it. The LOCK TIMEOUT is a time limit for how long the blocked database request will wait for the resource to be released.  If it is not released in time, then an error is raised.  

The database chooses what kind of locks to use and how long to hold them based on a complex interaction of factors: transaction isolation level, type of database request, transaction boundaries, locking hints, other locks already in effect, and so forth.  It's a bit much to get your head around the details on a case-by-case basis. I cannot even pretend to do so; that's why it's so important to develop an over-arching locking strategy that guides your application development.  

I cannot advise you on a specific value to use for LOCK TIMEOUT.   If LOCK TIMEOUT is really set to -1, then you NEVER get the LOCK TIMEOUT ERROR, but then users may end up waiting and waiting and waiting for their requests to complete.  If you set it to 0, then you get the error immediately.  Determining the appropriate lock timeout needs to be driven from your service  level agreement or user expectations.  Short of that, you might start with something on the order of 3-5 seconds (3000) and then tune it to a happy balance: increase if you get too many error complaints and decrease if you get too many slow response time complaints.

The two errors that you've identified are related to the insert and update statements that you've already identified.  Simply put, one user is executing those statements against a resource that is locked by another user.  That may be a good thing, because you really don't want two users stepping on each other's toes.
And without a radical redesign, it's bound to happen from time-to-time given the inherent nature of multiuser applications.  I think your job is to minimize how often it happens and provide a more friendly response when it does.

Since locks are held for the duration of a transaction, one thing to look for is long-running transactions. That can happen from batch process running concurrently or from unbalanced begin/commit transaction statements.  Now that you know what table is involved, you can monitor locks on that table with this script


declare @tab table (spid int, dbid int, objid int, indid int, type varchar(100), Resource varchar(100), mode varchar(100), Status varchar(10))
insert into @tab exec sp_lock  
select * from @tab where objid=object_id('shipment')

Open in new window



I encourage you to gather a little more information and then come back to the EE with a question specific to one lock timeout error.
0
 

Author Comment

by:MiamiTec
ID: 37823083
Yes, I agree that the source of the "-6 errors" has been identified.

The LOCK TIMEOUT is definitely set to -1 in the MS SQL Server Management Studio options (see the photo), but maybe that only sets LOCK TIMEOUT for my Management Studio session or there is something overriding it.  

I added a SET LOCK_TIMEOUT 5000 statement to the UpdateShipmentFromReceiving stored procedure and will see how it goes from there.

Thank you for the sp_lock script and I will definitely ask another question if I my LOCK_TIMEOUT woes turn out to be a mystery!


LOCK TIMEOUT SETTING
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now