C# Data Delete and insert from winform application throwing dead lock error

  • Thread starter Thread starter Sudip_inn
  • Start date Start date
S

Sudip_inn

Guest
i have store procedure in sql server where i have BEGIN TRAN and COMMIT TRAN

with in BEGIN TRAN and COMMIT TRAN i am first deleting data from few tables and after that i insert data into those tables

with in cursor loop.

basically from my c# application i am sending xml to store procedure and SP load that xml into cursor and insert data into table row by row. now very recently i notice when i am calling this store procedure then i am getting dead lock error.

screen shot attached

1618280.png

so here is my SP code which is bit long.

USE [RDSS_WB]
GO
/****** Object: StoredProcedure [dbo].[USP_CRSSaveTickerBogey] Script Date: 9/11/2020 11:19:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--<?xml version="1.0" encoding="utf-16"?>
--<Root>
-- <PeriodicalData>
-- <Section>Consensus Model</Section>
-- <LineItem>Net Sales</LineItem>
-- <XFundCode>TRIN</XFundCode>
-- <StandardDate>2010 FY</StandardDate>
-- <StandardValue>65225</StandardValue>
-- </PeriodicalData>
-- <PeriodicalData>
-- <Section>Consensus Model</Section>
-- <LineItem>Net Sales</LineItem>
-- <XFundCode>TRIN</XFundCode>
-- <StandardDate>2011 FY</StandardDate>
-- <StandardValue>108249</StandardValue>
-- </PeriodicalData>
--</Root>

--<?xml version="1.0" encoding="utf-16"?>
--<Root>
-- <TenQKLiConfig>
-- <Ticker>TDY1</Ticker>
-- <Section>Consensus Model</Section>
-- <LI>Cost of Revenue</LI>
-- <StandrdFormula />
-- <StandrdFormulaActual />
-- <AllowedDecimalPlace>1</AllowedDecimalPlace>
-- <CurrencySign>$</CurrencySign>
-- <CurrencyCode>en-US</CurrencyCode>
-- <AllowPercentageSign>false</AllowPercentageSign>
-- <AllowComma>true</AllowComma>
-- <QCCheck>false</QCCheck>
-- <QCType>SUMQ</QCType>
-- <BlueMatrix1stElementFormula>"Consensus Model~Cost of Revenue~3330"/"Consensus Model~Net Revenue~5833"</BlueMatrix1stElementFormula>
-- <DevelopmentStage />
-- <CrossCalc1Q />
-- <CrossCalc2Q />
-- <CrossCalc3Q />
-- <CrossCalc4Q />
-- <CrossCalcFY />
-- <GH_FontStyle>Regular</GH_FontStyle>
-- <GH_Strikeout>false</GH_Strikeout>
-- <FontStyle>Regular</FontStyle>
-- <Strikeout>false</Strikeout>
-- </TenQKLiConfig>
-- </Root>

ALTER PROCEDURE [dbo].[USP_CRSSaveTickerBogey]
@TickerID varchar(20),
@UserID varchar(20),
@BogyXML xml,
@LiConfigXML XML,
@STATUS VARCHAR(200) OUTPUT
AS

BEGIN
DECLARE @XMLFormat AS INT,@SectionOrder AS INT
DECLARE @section VARCHAR(100), @LineItem VARCHAR(100), @StandardDate VARCHAR(20), @StandardValue VARCHAR(20), @XFundCode VARCHAR(20),@ActualProvidedByCompany VARCHAR(3)
DECLARE @MasterID INT, @SectionID INT, @LineItemID INT, @PeriodID INT, @OrderID INT,@ConfigID INT

DECLARE @TmpStandrdFormula VARCHAR(MAX)='',
@TmpStandrdFormulaActual VARCHAR(MAX)='',
@TmpBlueMatrix1stElementFormula VARCHAR(MAX)='',
@TmpCrossCalc1Q VARCHAR(MAX)='',
@TmpCrossCalc2Q VARCHAR(MAX)='',
@TmpCrossCalc1H VARCHAR(MAX)='',
@TmpCrossCalc3Q VARCHAR(MAX)='',
@TmpCrossCalc4Q VARCHAR(MAX)='',
@TmpCrossCalc2H VARCHAR(MAX)='',
@TmpCrossCalcFY VARCHAR(MAX)=''

DECLARE @LI VARCHAR(100),
@StandrdFormula VARCHAR(MAX),
@StandrdFormulaActual VARCHAR(MAX),
@AllowedDecimalPlace VARCHAR(5),
@CurrencySign NVARCHAR(5),
@CurrencyCode VARCHAR(10),
@AllowPercentageSign VARCHAR(10),
@AllowComma VARCHAR(5),
@QCCheck VARCHAR(5),
@QCType VARCHAR(10),
@BlueMatrix1stElement VARCHAR(MAX),
@BlueMatrix1stElementFormula VARCHAR(MAX),
@DevelopmentStage VARCHAR(MAX),
@CrossCalc1Q VARCHAR(MAX),
@CrossCalc2Q VARCHAR(MAX),
@CrossCalc1H VARCHAR(MAX),
@CrossCalc3Q VARCHAR(MAX),
@CrossCalc4Q VARCHAR(MAX),
@CrossCalc2H VARCHAR(MAX),
@CrossCalcFY VARCHAR(MAX),
@SummaryTab VARCHAR(MAX)

SET @LI =''
SET @StandrdFormula =''
SET @StandrdFormulaActual =''
SET @AllowedDecimalPlace =''
SET @CurrencySign =''
SET @CurrencyCode =''
SET @AllowPercentageSign =''
SET @AllowComma =''
SET @QCCheck =''
SET @QCType =''
SET @BlueMatrix1stElement = ''
SET @BlueMatrix1stElementFormula =''
SET @DevelopmentStage =''
SET @CrossCalc1Q =''
SET @CrossCalc2Q =''
SET @CrossCalc3Q =''
SET @CrossCalc4Q =''
SET @CrossCalcFY =''
SET @CrossCalc1H = ''
SET @CrossCalc2H = ''
SET @SummaryTab = ''

SET @MasterID=0
SET @SectionID=0
SET @LineItemID=0
SET @PeriodID=0
SET @OrderID=0
SET @ConfigID=0

SET @Section=''
SET @LineItem=''
SET @StandardDate=''
SET @StandardValue=''
SET @XFundCode=''

SET @SectionOrder=1

BEGIN TRY
SET QUOTED_IDENTIFIER OFF;
BEGIN TRAN

BEGIN --Delete all ticker specific data first

--DELETE c
--FROM TblLineItemTemplate a INNER JOIN
--tblTicker_LiConfig c ON a.ID = c.LineItemID INNER JOIN
--tblSectionTemplate b ON c.SectionID = b.SectionID
--INNER JOIN tblCalenderMaster cm ON cm.ID=c.MasterID
--WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID AND cm.TickerID=@TickerID

--Deleting data from tblTicker_Bogey
DELETE c
FROM TblLineItemTemplate a INNER JOIN
tblTicker_Bogey c ON a.ID = c.LineItemID INNER JOIN
tblSectionTemplate b ON c.SectionID = b.SectionID
WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID

SELECT @MasterID =ID FROM tblCalenderMaster WHERE TRIM(TickerID)=TRIM(@TickerID)
--Deleting data from tblTicker_LiConfig
DELETE FROM tblTicker_LiConfig WHERE TRIM(TickerID)=TRIM(@TickerID)

--Deleting data from tblSectionLineItemTemplate
DELETE c
FROM TblLineItemTemplate a INNER JOIN
tblSectionLineItemTemplate c ON a.ID = c.LineItemID INNER JOIN
tblSectionTemplate b ON c.SectionID = b.SectionID
WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID

DELETE FROM TblLineItemTemplate WHERE TickerID=@TickerID
DELETE FROM tblSectionTemplate WHERE TickerID=@TickerID

DELETE d
FROM tblCalenderDetail d INNER JOIN tblCalenderMaster M
ON D.MasterID=M.ID
WHERE M.TickerID=@TickerID

DELETE FROM tblCalenderMaster WHERE TickerID=@TickerID
END




IF NOT EXISTS(SELECT * FROM tblCalenderMaster WHERE TRIM(TickerID)=TRIM(@TickerID))
BEGIN
INSERT INTO tblCalenderMaster(TickerID,Following53Weeks,TransitionPeriod)
VALUES(@TickerID,'N','N')

SELECT @MasterID = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT @MasterID = ID FROM tblCalenderMaster WHERE TRIM(TickerID)=TRIM(@TickerID)
END

--IF @MasterID > 0
--BEGIN
-- DELETE FROM tblCalenderDetail WHERE MasterID=@MasterID
-- DELETE FROM tblTicker_Bogey WHERE MasterID=@MasterID
--END

--load xml data into cursor
Exec sp_xml_preparedocument @XMLFormat OUTPUT, @BogyXML

DECLARE CURRECORD
CURSOR LOCAL FOR
SELECT Section,LineItem,StandardDate,StandardValue,XFundCode,ActualProvidedByCompany
FROM OPENXML (@XMLFORMAT, '/Root/PeriodicalData', 2)
WITH
(
Section VARCHAR(100),
LineItem VARCHAR(100),
StandardDate VARCHAR(20),
StandardValue VARCHAR(20),
XFundCode VARCHAR(20),
ActualProvidedByCompany VARCHAR(3)
)

-- open cursor
OPEN CURRECORD
FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode,@ActualProvidedByCompany

-- iterate in cursor to fetch value
WHILE (@@FETCH_STATUS=0)
BEGIN

--REPLACE(TRIM(@LineItem),'''','''''')
--CHR(39)

--PRINT @LineItem

IF NOT EXISTS(SELECT * FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LineItem))
BEGIN
--IF once a xfund code inserted into TblLineItemTemplate table for a specific line item and if same xfund comes again for same line item
--then prevent it from here to make duplicate xfund code
--IF EXISTS(SELECT * FROM TblLineItemTemplate WHERE UPPER(TRIM(LineItem)) = UPPER(TRIM(@LineItem)) AND TickerID = @TickerID AND XFundCode=@XFundCode)
--BEGIN
-- SET @XFundCode=''
--END

INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),TRIM(@LineItem),TRIM(@XFundCode),'I',@UserID)
SET @LineItemID = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LineItem)
END


IF NOT EXISTS(SELECT * FROM tblSectionTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(Section)=TRIM(@section))
BEGIN
INSERT INTO tblSectionTemplate(TickerID,Section,Active,OrderID)
VALUES(TRIM(@TickerID),TRIM(@section),'A',@SectionOrder)

SET @SectionOrder = @SectionOrder + 1

-- storing identity value of last inserted item
SELECT @SectionID = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT @SectionID =SectionID FROM tblSectionTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(Section)=TRIM(@section)
END


IF NOT EXISTS(SELECT * FROM tblSectionLineItemTemplate WHERE SectionID=@SectionID AND LineItemID=@LineItemID)
BEGIN

IF EXISTS(SELECT * FROM tblSectionLineItemTemplate WHERE SectionID=@SectionID)
BEGIN
SELECT @OrderID=MAX(OrderID)+1 FROM tblSectionLineItemTemplate WHERE SectionID=@SectionID
END
ELSE
BEGIN
SET @OrderID= 1
END

INSERT INTO tblSectionLineItemTemplate(SectionID,LineItemID,Active,InsertDate,UserID,OrderID)
VALUES(@SectionID,@LineItemID,'A',GetDate(),@UserID,@OrderID)

END

IF NOT EXISTS(SELECT * FROM tblCalenderDetail WHERE MasterID=@MasterID AND TRIM(Period)=TRIM(@StandardDate))
BEGIN
INSERT INTO tblCalenderDetail(MasterID,Period,IsDeleted)
VALUES(@MasterID,TRIM(@StandardDate),'N')

SELECT @PeriodID = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT @PeriodID=ID FROM tblCalenderDetail WHERE MasterID=@MasterID AND TRIM(Period)=TRIM(@StandardDate)
END


--IF NOT EXISTS(SELECT * FROM tblTicker_Bogey WHERE MasterId=@MasterID AND SectionID=@SectionID AND LineItemID=@LineItemID AND PeriodID=@PeriodID)
--BEGIN
IF IIF(TRIM(@StandardValue) <> '',TRIM(@StandardValue),'') <> ''
BEGIN
INSERT INTO tblTicker_Bogey (MasterID,SectionID,LineItemID,PeriodID,[Values],UserID,InsertedOn,ModifyOn,status,ActualProvidedByCompany)
VALUES(@MasterID,@SectionID,@LineItemID,@PeriodID,TRIM(@StandardValue),@UserID,GetDate(),NULL,'I',@ActualProvidedByCompany)

PRINT 'Bogey Inserted'+CAST(@MasterID AS VARCHAR)

END

--PRINT @Section+' '+ @LineItem+' '+ @StandardDate+' '+ IIF(@StandardValue<>'',@StandardValue,'No')
--PRINT CAST(@SectionID AS VARCHAR)+' '+ CAST(@LineItemID AS VARCHAR)+' '+ CAST(@PeriodID AS VARCHAR)+' '+ CAST(@SectionID AS VARCHAR)

--END

SET @SectionID=0
SET @LineItemID=0
SET @PeriodID=0
--SET @OrderID=0

SET @Section=''
SET @LineItem=''
SET @StandardDate=''
SET @StandardValue=''
SET @XFundCode=''
FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode,@ActualProvidedByCompany
END


CLOSE CURRECORD
DEALLOCATE CURRECORD

SET @SectionID =0
SET @LineItemID =0
SET @MasterID=0
--Insert data into tblTicker_LiConfig
--load xml data into cursor
Exec sp_xml_preparedocument @XMLFormat OUTPUT, @LiConfigXML

DECLARE CURRECORD
CURSOR LOCAL FOR
SELECT Section,LI,StandrdFormula,StandrdFormulaActual,AllowedDecimalPlace,CurrencySign,CurrencyCode,AllowPercentageSign,AllowComma,QCCheck,QCType,
BlueMatrix1stElement,BlueMatrix1stElementFormula,DevelopmentStage,
CrossCalc1Q,CrossCalc2Q,CrossCalc1H,CrossCalc3Q,CrossCalc4Q,CrossCalc2H,CrossCalcFY,SummaryTab

FROM OPENXML (@XMLFORMAT, '/Root/TenQKLiConfig', 2)
WITH
(
Section VARCHAR(100),
LI VARCHAR(100),
StandrdFormula VARCHAR(MAX),
StandrdFormulaActual VARCHAR(MAX),
AllowedDecimalPlace VARCHAR(5),
CurrencySign NVARCHAR(5),
CurrencyCode VARCHAR(10),
AllowPercentageSign VARCHAR(10),
AllowComma VARCHAR(5),
QCCheck VARCHAR(5),
QCType VARCHAR(10),
BlueMatrix1stElement VARCHAR(MAX),
BlueMatrix1stElementFormula VARCHAR(MAX),
DevelopmentStage VARCHAR(MAX),
CrossCalc1Q VARCHAR(MAX),
CrossCalc2Q VARCHAR(MAX),
CrossCalc1H VARCHAR(MAX),
CrossCalc3Q VARCHAR(MAX),
CrossCalc4Q VARCHAR(MAX),
CrossCalc2H VARCHAR(MAX),
CrossCalcFY VARCHAR(MAX),
SummaryTab VARCHAR(MAX)

)

SELECT @MasterID =ID FROM tblCalenderMaster WHERE TRIM(TickerID)=TRIM(@TickerID)

--DELETE FROM tblTicker_LiConfig WHERE MasterID=@MasterID

-- open cursor
OPEN CURRECORD
FETCH NEXT FROM CURRECORD INTO @Section,@LI,@StandrdFormula,@StandrdFormulaActual,@AllowedDecimalPlace,@CurrencySign,@CurrencyCode,@AllowPercentageSign,@AllowComma,@QCCheck,@QCType
,@BlueMatrix1stElement,@BlueMatrix1stElementFormula,@DevelopmentStage,@CrossCalc1Q,@CrossCalc2Q,@CrossCalc1H,@CrossCalc3Q,@CrossCalc4Q,@CrossCalc2H,@CrossCalcFY,@SummaryTab

-- iterate in cursor to fetch value
WHILE (@@FETCH_STATUS=0)
BEGIN

SELECT @SectionID =SectionID FROM tblSectionTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(Section)=TRIM(@section)
SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LI)

SET @TmpStandrdFormula=@StandrdFormula
SELECT @StandrdFormula=dbo.fn_TranslateFormulaToID(@TickerID,@StandrdFormula,'N')

SELECT @StandrdFormulaActual=dbo.fn_TranslateFormulaToID(@TickerID,@StandrdFormulaActual,'N')

SET @TmpBlueMatrix1stElementFormula = @BlueMatrix1stElementFormula
SELECT @BlueMatrix1stElementFormula=dbo.fn_TranslateFormulaToID(@TickerID,@BlueMatrix1stElementFormula,'N')

SET @TmpCrossCalc1Q = @CrossCalc1Q
SET @TmpCrossCalc2Q = @CrossCalc2Q
SET @TmpCrossCalc1H = @CrossCalc1H
SET @TmpCrossCalc3Q = @CrossCalc3Q
SET @TmpCrossCalc4Q = @CrossCalc4Q
SET @TmpCrossCalc2H = @CrossCalc2H
SET @TmpCrossCalcFY = @CrossCalcFY

SELECT @CrossCalc1Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc1Q,'Y')
SELECT @CrossCalc2Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc2Q,'Y')
SELECT @CrossCalc1H=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc1H,'Y')
SELECT @CrossCalc3Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc3Q,'Y')
SELECT @CrossCalc4Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc4Q,'Y')
SELECT @CrossCalc2H=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc2H,'Y')
SELECT @CrossCalcFY=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalcFY,'Y')


IF @SectionID > 0 AND @LineItemID > 0
BEGIN
IF NOT EXISTS(SELECT * FROM tblTicker_LiConfig WHERE SectionID=@SectionID AND LineItemID=@LineItemID)
BEGIN
INSERT INTO tblTicker_LiConfig
(
MasterID,
SectionID,
LineItemID,
StandrdFormula,
StandrdFormulaActual,
AllowedDecimalPlace,
CurrencySign,
CurrencyCode,
AllowPercentageSign,
AllowComma,
QCCheck,
QCType,
BlueMatrix1stElement,
BlueMatrix1stElementFormula,
DevelopmentStage,
CrossCalc1Q,
CrossCalc2Q,
CrossCalc1H,
CrossCalc3Q,
CrossCalc4Q,
CrossCalc2H,
CrossCalcFY,
SummaryTab,
TickerID
)
VALUES
(
@MasterID,
@SectionID,
@LineItemID,
@StandrdFormula,
@StandrdFormulaActual,
@AllowedDecimalPlace,
@CurrencySign,
@CurrencyCode,
@AllowPercentageSign,
@AllowComma,
@QCCheck,
@QCType,
TRIM(@BlueMatrix1stElement),
@BlueMatrix1stElementFormula,
@DevelopmentStage,
@CrossCalc1Q,
@CrossCalc2Q,
ISNULL(@CrossCalc1H,''),
@CrossCalc3Q,
@CrossCalc4Q,
ISNULL(@CrossCalc2H,''),
@CrossCalcFY,
@SummaryTab,
@TickerID
)

SELECT @ConfigID = SCOPE_IDENTITY()
PRINT 'liconfig Inserted'+CAST(@MasterID AS VARCHAR)

IF @TmpStandrdFormula <> '' AND @StandrdFormula = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is Standard Formula parse during insertion',
'TickerID '+@TickerID+' Standard Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpStandrdFormula,GETDATE())
END

IF TRIM(@TmpBlueMatrix1stElementFormula) <> '' AND @BlueMatrix1stElementFormula = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is BlueMatrix Formula parse during insertion',
'TickerID '+@TickerID+' BlueMatrix1stElement Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpBlueMatrix1stElementFormula,GETDATE())
END

IF TRIM(@TmpCrossCalc1Q) <> '' AND @CrossCalc1Q = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal1 Formula parse during insertion',
'TickerID '+@TickerID+' CrossCal1 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc1Q,GETDATE())
END

IF TRIM(@TmpCrossCalc2Q) <> '' AND @CrossCalc2Q = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal2 Formula parse during insertion',
'TickerID '+@TickerID+' CrossCal2 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc2Q,GETDATE())
END

IF TRIM(@TmpCrossCalc1H) <> '' AND @CrossCalc1H = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal2 Formula parse during insertion',
'TickerID '+@TickerID+' CrossCal2 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc1H,GETDATE())
END

IF TRIM(@TmpCrossCalc3Q) <> '' AND @CrossCalc3Q = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal3 Formula parse during insertion',
'TickerID '+@TickerID+' CrossCal3 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc3Q,GETDATE())
END

IF TRIM(@TmpCrossCalc4Q) <> '' AND @CrossCalc4Q = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal4 Formula parse during insertion',
'TickerID '+@TickerID+' CrossCal4 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc4Q,GETDATE())
END

IF TRIM(@TmpCrossCalc2H) <> '' AND @CrossCalc2H = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal2 Formula parse during insertion',
'TickerID '+@TickerID+' CrossCal2 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc2H,GETDATE())
END

IF TRIM(@TmpCrossCalcFY) <> '' AND @CrossCalcFY = ''
BEGIN
INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCalFY Formula parse during insertion',
'TickerID '+@TickerID+' CrossCalFY Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalcFY,GETDATE())
END

END
END

SET @ConfigID=0
SET @StandrdFormula = ''
SET @BlueMatrix1stElementFormula = ''
SET @CrossCalc1Q = ''
SET @CrossCalc2Q = ''
SET @CrossCalc1H = ''
SET @CrossCalc3Q = ''
SET @CrossCalc4Q = ''
SET @CrossCalc2H = ''
SET @CrossCalcFY = ''
SET @TmpCrossCalcFY = ''
SET @SummaryTab =''

SET @QCCheck = ''
SET @QCType = ''
SET @BlueMatrix1stElement = ''
SET @DevelopmentStage = ''
SET @AllowedDecimalPlace = ''
SET @CurrencySign = ''
SET @CurrencyCode = ''
SET @AllowPercentageSign = ''
SET @AllowComma = ''
SET @section = ''
SET @LI = ''

FETCH NEXT FROM CURRECORD INTO @Section,@LI,@StandrdFormula,@StandrdFormulaActual,@AllowedDecimalPlace,@CurrencySign,@CurrencyCode,@AllowPercentageSign,@AllowComma,@QCCheck,@QCType
,@BlueMatrix1stElement,@BlueMatrix1stElementFormula,@DevelopmentStage,@CrossCalc1Q,@CrossCalc2Q,@CrossCalc1H,@CrossCalc3Q,@CrossCalc4Q,@CrossCalc2H,@CrossCalcFY,@SummaryTab

END

CLOSE CURRECORD
DEALLOCATE CURRECORD
COMMIT TRAN

SET @STATUS='SUCCESS'
SET QUOTED_IDENTIFIER ON;
END TRY
BEGIN CATCH
--PRINT 'err'
ROLLBACK TRAN

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE() + ' occurred at Line_Number: ' + CAST(ERROR_LINE() AS VARCHAR(50)),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

SET @STATUS='FAIL'
PRINT 'Err---> '+ @ErrorMessage
RAISERROR
(
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END



so when 80 users using my application and all click on button which call this SP then this dead lock appear.

please tell me a good suggestion to fix this problem. what i need to change in my c# code and in sql server end SP code.

i also use command timeout = 0 is it good approach to use command timeout = 0

please give me right solution to get rid of this error. thanks

Continue reading...
 
Back
Top