نوشته شده توسط
ShayanFiroozi
سلام ،
SET LOCK_TIMEOUT 1800;
SELECT @@LOCK_TIMEOUT AS [Lock Timeout];
توجه بفرمایین که تایم به میلی ثانیه هست ،
در ضمن اگر ممکنه
کد پروسیجر رو هم ارسال کنین ، شاید مشکل از جای دیگست که
Suspend میشه.
USE [Mana_Boresh_CNC1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[INSERT_LantekData]
AS
BEGIN
DECLARE
@CustId int,@CustName nvarchar(255),@JobNo varchar(30), @LastDate datetime,@final int,
@ProjectId bigint,@JobId bigint,@RC varchar(max),@Ret int,
@LogText nvarchar(max)
BEGIN TRANSACTION
BEGIN TRY
select TOP(1) @CustId=ISNULL(tblCustomers.id,-1),@CustName=tbl.Name,@JobNo=tbl.JobRef,@LastDate= tbl.LastDate,@final=ISNULL(tbl.Final,0),@ProjectId =tbl.ProjectId,@JobId=tbl.JobId from (
select vwLantekLast.Name,JobRef,LastDate,Final,tblProject s.id ProjectId,tblLantekJobs.id JobId
from vwLantekLast,tblLantekJobs,tblProjects,(SELECT MAX(InsertDT) InsertDT,JobId from tblLantekFactorDetail group by JobId)FacDet
where vwLantekLast.JobRef=tblLantekJobs.JobNo AND FacDet.JobId=tblLantekJobs.id AND tblProjects.Id=tblLantekJobs.ProjectId AND LastDate>InsertDT
union
select *,CAST(0 as bit) Final,null ProjectId,null JobId
from vwLantekLast
where vwLantekLast.JobRef not in (select JobNo from tblLantekJobs) and
(LastDate>'2022-10-23' OR ((LastDate>'2022-03-21')AND(Name='MANA PERSHIA' OR Name='MANA KHADAMAT' OR Name='SEMSAR' OR Name='KWC')))
)tbl
left join tblCustomers on tblCustomers.CustomerNameLantek=tbl.Name COLLATE Arabic_CI_AS
where Final=0 AND LastDate<DATEADD(MI,-1,SYSDATETIME())
order by LastDate desc
IF @LastDate IS NOT NULL BEGIN
IF @CustId=-1 BEGIN
INSERT INTO tblCustomers(CustomerCode,CustomerName,CustomerNam eLantek)VALUES(
(SELECT MAX(CustomerCode)+1 FROM tblCustomers),@CustName,@CustName)
SELECT @CustId=@@IDENTITY
SET @LogText=N'Add Customer='+@CustName
EXECUTE @Ret=dbo.WriteLog @Description=@LogText
END
SELECT @ProjectId=T1.Id FROM tblProjects T1 LEFT JOIN tblLantekJobs T2 ON T1.Id=T2.ProjectId WHERE JobNo=@JobNo
IF @ProjectId IS NULL BEGIN
INSERT INTO tblProjects(RegTime,Name,CustomerId,UserId,OrderTi me,SheetCut,Brake,PipeCut,Weld,MaterialMana,Materi alCustomer,MaterialGet)VALUES(SYSDATETIME(),
@JobNo,@CustId,15,@LastDate,1,0,0,0,0,0,0)
SELECT @ProjectId=@@IDENTITY
SET @LogText=N'Add Project='+@JobNo
EXECUTE @Ret=dbo.WriteLog @Description=@LogText
END
SELECT @JobId=Id FROM tblLantekJobs WHERE JobNo=@JobNo
IF @JobId IS NULL BEGIN
INSERT INTO tblLantekJobs(InsertDateTime,JobNo,Name,ProjectId, ProvidedQuantity)VALUES(SYSDATETIME(),
@JobNo,@CustName,@ProjectId,
(SELECT SUM(DIS_NQ) FROM REM.CUT1401.dbo.[MMNN_MMOO_00000300] WHERE DIS_JobRef=@JobNo))
SELECT @JobId=@@IDENTITY
SET @LogText=N'Add Job='+@JobNo
EXECUTE @Ret=dbo.WriteLog @Description=@LogText
END
EXECUTE @RC = [dbo].[INSERT_LantekFactorDetailsAndMetrial] @JobNo
SET @LogText=N'Add Lantek Details='+@JobNo
EXECUTE @Ret=dbo.WriteLog @Description=@LogText
IF @RC<>'0' BEGIN
SET @LogText=N'Error in [INSERT_LantekFactorDetailsAndMetrial] Message='+@RC
EXECUTE @Ret=dbo.WriteLog @Description=@LogText,@LogTypeId=2
END
END
UPDATE tblVar SET Value=SYSDATETIME() WHERE Name='LastAlive'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_MESSAGE()
SET @LogText=ERROR_MESSAGE()
EXECUTE @Ret=dbo.WriteLog @Description=@LogText,@LogTypeId=2
END CATCH
END