asked on

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

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...

dqmq

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.

MiamiTec

ASKER

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

dqmq

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

MiamiTec

ASKER

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?

MiamiTec

ASKER

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.

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MiamiTec

ASKER

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

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MiamiTec

ASKER

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!

MiamiTec

ASKER

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?

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MiamiTec

ASKER

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!