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