August 12, 2010 at 3:01 am
Hi,
When I am trying to run the below procedure, and at the same time if other user is also accessing the same table then getting the primary key violation error. Can you tell me how we can lock the table for this transaction and once its done unlock thetable.
USE [KRM]
GO
/****** Object: StoredProcedure [dbo].[KNLEventCopy] Script Date: 08/12/2010 08:28:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[KNLEventCopy] (@startdatetime datetime, @enddatetime datetime)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
begin transaction
/*
Deleting all temporary tables used during the migration script
*/
--Getting the hours irrespective of summer or winter time difference
DECLARE @hhtime as int
--SELECT @hhtime=time from navcountry where country=@cntry and season='Summer'
--select @hhtime =
select @hhtime=datediff(hh,getutcdate(),getdate())
select @hhtime=@hhtime+1
--patch end
IF OBJECT_ID('tempdb..#Events') is not null DROP TABLE #Events
DECLARE @Seed bigint
SELECT @Seed = ISNULL(MAX(Event_PK) + 1, 1) FROM Event_2
--/* Get list of Events to copy */
SELECT [EquipmentNumber_FK]
,[StartTimestamp]
,[RemoteMonitoringType_FK]
,[MessageType_FK]
,[CEN]
,[Floor]
,[EndTimestamp]
,[FailureClassDelay]
,[EquipmentMovingStatus]
,[OperationMode]
,[DoorZone]
,[DoorStatus]
,[DriveStatus]
,[CarLoad]
,[Comment]
,[DriveDetectedFault1]
,[DriveDetectedFault2]
,[DriveDetectedFault3]
,[FaultClass]
,[SourceUpper]
,[SourceLower]
,[SourceLeft]
,[SourceRight]
,[FaultCurrentlyActive]
,[FaultShutsDown]
,[FaultNeedsManualReset]
,[FaultIsEmergencyStop]
,[FaultInDevice1]
,[FaultInDevice2]
,[FaultInDevice3]
,[FaultInDevice4]
,[FaultInDevice5]
,[FaultInDevice6]
,[FaultInDevice7]
,[FaultInDevice8]
,[BrakeSlidingDistance]
,[StoppingDistanceData]
,[Knx3ExternalInput1]
,[Knx3ExternalInput2]
,[Knx3SystemFailureReason_FK]
,[ReportCall]
,[ReportCallHandledby]
,[CalculatedEndTimestamp]
,[FrontEndID]
,[CEN_EquipmentStatus]
,[CreateDate]
,[Creator]
INTO #Events
FROM Event_1
WHERE1=2
ALTER TABLE #Events
ADD Event_PK int IDENTITY(1,1)
DBCC CHECKIDENT (#Events,RESEED, @Seed)
--code for thesting needs to be removed after that
--SELECT EquipmentNumber_FK, DATEADD(hh, - @hhtime, StartTimestamp) AS StartTimestamp, RemoteMonitoringType_FK, MessageType_FK, CEN, Floor,
-- DATEADD(hh, - @hhtime, EndTimestamp) AS EndTimestamp, FailureClassDelay, EquipmentMovingStatus, OperationMode, DoorZone, DoorStatus, DriveStatus,
-- CarLoad, Comment, DriveDetectedFault1, DriveDetectedFault2, DriveDetectedFault3, FaultClass, SourceUpper, SourceLower, SourceLeft, SourceRight,
-- FaultCurrentlyActive, FaultShutsDown, FaultNeedsManualReset, FaultIsEmergencyStop, FaultInDevice1, FaultInDevice2, FaultInDevice3,
-- FaultInDevice4, FaultInDevice5, FaultInDevice6, FaultInDevice7, FaultInDevice8, BrakeSlidingDistance, StoppingDistanceData, Knx3ExternalInput1,
-- Knx3ExternalInput2, Knx3SystemFailureReason_FK, ReportCall, ReportCallHandledby, CalculatedEndTimestamp, FrontEndID, CEN_EquipmentStatus,
-- DATEADD(hh, - @hhtime, CreateDate) AS CreateDate, 'KNXMCopy' AS Creator
--INTO Test
--FROM TNLNTS30.KRM.dbo.Event AS Event_1
--WHERE (NOT ((CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar)) IN
-- (SELECT TOP (100) PERCENT CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar) AS EventID
-- FROM Event
-- ORDER BY CreateDate DESC))) AND (DATEADD(hh, - @hhtime, CreateDate) > CONVERT(DATETIME, @startdatetime, 102) AND (DATEADD(hh, - @hhtime, CreateDate) < CONVERT(DATETIME, @enddatetime, 102)))
--ORDER BY DATEADD(hh, - @hhtime, CreateDate) DESC
--end of testing code
INSERT INTO [#Events]
([EquipmentNumber_FK]
,[StartTimestamp]
,[RemoteMonitoringType_FK]
,[MessageType_FK]
,[CEN]
,[Floor]
,[EndTimestamp]
,[FailureClassDelay]
,[EquipmentMovingStatus]
,[OperationMode]
,[DoorZone]
,[DoorStatus]
,[DriveStatus]
,[CarLoad]
,[Comment]
,[DriveDetectedFault1]
,[DriveDetectedFault2]
,[DriveDetectedFault3]
,[FaultClass]
,[SourceUpper]
,[SourceLower]
,[SourceLeft]
,[SourceRight]
,[FaultCurrentlyActive]
,[FaultShutsDown]
,[FaultNeedsManualReset]
,[FaultIsEmergencyStop]
,[FaultInDevice1]
,[FaultInDevice2]
,[FaultInDevice3]
,[FaultInDevice4]
,[FaultInDevice5]
,[FaultInDevice6]
,[FaultInDevice7]
,[FaultInDevice8]
,[BrakeSlidingDistance]
,[StoppingDistanceData]
,[Knx3ExternalInput1]
,[Knx3ExternalInput2]
,[Knx3SystemFailureReason_FK]
,[ReportCall]
,[ReportCallHandledby]
,[CalculatedEndTimestamp]
,[FrontEndID]
,[CEN_EquipmentStatus]
,[CreateDate]
,[Creator])
SELECT EquipmentNumber_FK, DATEADD(hh, - @hhtime, StartTimestamp) AS StartTimestamp, RemoteMonitoringType_FK, MessageType_FK, CEN, Floor,
DATEADD(hh, - @hhtime, EndTimestamp) AS EndTimestamp, FailureClassDelay, EquipmentMovingStatus, OperationMode, DoorZone, DoorStatus, DriveStatus,
CarLoad, Comment, DriveDetectedFault1, DriveDetectedFault2, DriveDetectedFault3, FaultClass, SourceUpper, SourceLower, SourceLeft, SourceRight,
FaultCurrentlyActive, FaultShutsDown, FaultNeedsManualReset, FaultIsEmergencyStop, FaultInDevice1, FaultInDevice2, FaultInDevice3,
FaultInDevice4, FaultInDevice5, FaultInDevice6, FaultInDevice7, FaultInDevice8, BrakeSlidingDistance, StoppingDistanceData, Knx3ExternalInput1,
Knx3ExternalInput2, Knx3SystemFailureReason_FK, ReportCall, ReportCallHandledby, CalculatedEndTimestamp, FrontEndID, CEN_EquipmentStatus,
DATEADD(hh, - @hhtime, CreateDate) AS CreateDate, 'KNXMCopy' AS Creator
FROM TNLNTS30.KRM.dbo.Event AS Event_1
WHERE (NOT ((CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar)) IN
(SELECT TOP (100) PERCENT CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar) AS EventID
FROM Event
ORDER BY CreateDate DESC))) AND (DATEADD(hh, - @hhtime, CreateDate) > CONVERT(DATETIME, @startdatetime, 102)AND (DATEADD(hh, - @hhtime, CreateDate) < CONVERT(DATETIME, @enddatetime, 102)))
ORDER BY DATEADD(hh, - @hhtime, CreateDate) DESC
INSERT INTO Event_2
([EquipmentNumber_FK]
,[StartTimestamp]
,[RemoteMonitoringType_FK]
,[MessageType_FK]
,[CEN]
,[Floor]
,[EndTimestamp]
,[FailureClassDelay]
,[EquipmentMovingStatus]
,[OperationMode]
,[DoorZone]
,[DoorStatus]
,[DriveStatus]
,[CarLoad]
,[Comment]
,[DriveDetectedFault1]
,[DriveDetectedFault2]
,[DriveDetectedFault3]
,[FaultClass]
,[SourceUpper]
,[SourceLower]
,[SourceLeft]
,[SourceRight]
,[FaultCurrentlyActive]
,[FaultShutsDown]
,[FaultNeedsManualReset]
,[FaultIsEmergencyStop]
,[FaultInDevice1]
,[FaultInDevice2]
,[FaultInDevice3]
,[FaultInDevice4]
,[FaultInDevice5]
,[FaultInDevice6]
,[FaultInDevice7]
,[FaultInDevice8]
,[BrakeSlidingDistance]
,[StoppingDistanceData]
,[Knx3ExternalInput1]
,[Knx3ExternalInput2]
,[Knx3SystemFailureReason_FK]
,[ReportCall]
,[ReportCallHandledby]
,[CalculatedEndTimestamp]
,[FrontEndID]
,[CEN_EquipmentStatus]
,[CreateDate]
,[Creator]
,[Event_PK])
Select * From #Events
commit transaction
DECLARE @Event_PK INT
EXEC KRM.dbo.GetNextKeyValue_p 'Event', @Event_PK OUTPUT,@@rowcount
Select * From #Events
Thanks in Advance
Naveen
August 12, 2010 at 5:08 am
I think you are not getting any replies because we have all seen the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
statement at the start of you script. Look up what this does in BOL, you are reading a table with uncommitted data to do a update query, not a good thing.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply