Sorun: Ayda 2-3 kere satış teklif yönetimi modülünün tabloları gece saat 00:00 gibi drop-create yapılmakta.
Job, trigger ve servisler kontrol edildi ilgili işlemi tetikleyen bir şey bulunamadı(tiger3e ver:2.76).
Arge devamlılığı için geçici çözüm olarak prosedur->job yaratıldı(yarım saatte bir) .
Teklif modülü tablolarında eğer data var ise yardımcı tabloya alınacak, eğer data yok ise(drop create olunca boşaldıysa) yardımcı tablolardan gerçek tablolara atacak.
CREATE PROCEDURE YT_KONTROL_SATISTEKLIFYONETIMI_TABLOLARI AS
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_SLSACTIV] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV] FROM [MYDB].[dbo].[LG_SLSACTIV]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SLSACTIV] ON
INSERT INTO [MYDB].[dbo].[LG_SLSACTIV]
(LOGICALREF,[DATE_]
,[TIME_]
,[ACTTYPE]
,[OPPORREF]
,[SALESMANREF]
,[CSTVNDREF]
,[ACTORDER]
,[DESCRIPTION]
,[PRIORITY]
,[APPROVAL]
,[DUEDATE]
,[DUETIME]
,[SENDVIA]
,[SENDREF]
,[SPECODE]
,[CYPHCODE]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[LASTREVISION]
,[TEXTINC]
,[DESCRIPTION2]
,[GUID])
SELECT LOGICALREF,[DATE_]
,[TIME_]
,[ACTTYPE]
,[OPPORREF]
,[SALESMANREF]
,[CSTVNDREF]
,[ACTORDER]
,[DESCRIPTION]
,[PRIORITY]
,[APPROVAL]
,[DUEDATE]
,[DUETIME]
,[SENDVIA]
,[SENDREF]
,[SPECODE]
,[CYPHCODE]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[LASTREVISION]
,[TEXTINC]
,[DESCRIPTION2]
,[GUID] FROM [MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SLSACTIV] OFF
END
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_SATI] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_SATI]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_SATI];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_SATI] FROM [MYDB].[dbo].[LG_SATI]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SATI] ON
INSERT INTO [MYDB].[dbo].[LG_SATI]
(LOGICALREF,[USERID]
,[CUSTOM]
,[ITEMTYP]
,[ITEMEXP]
,[PARENTREF]
,[HASCHILD]
,[FILTERREF])
SELECT LOGICALREF,[USERID]
,[CUSTOM]
,[ITEMTYP]
,[ITEMEXP]
,[PARENTREF]
,[HASCHILD]
,[FILTERREF] FROM [MYDB].[dbo].[YT_RESTORE_LG_SATI]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SATI] OFF
END
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_CVARPASG] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_CVARPASG]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_CVARPASG];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_CVARPASG] FROM [MYDB].[dbo].[LG_CVARPASG]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CVARPASG] ON
INSERT INTO [MYDB].[dbo].[LG_CVARPASG]
(LOGICALREF,[CSTVNDREF]
,[ARPREF]
,[FIRMNO]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[DEFAULTFLG])
SELECT LOGICALREF,[CSTVNDREF]
,[ARPREF]
,[FIRMNO]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[DEFAULTFLG] FROM [MYDB].[dbo].[YT_RESTORE_LG_CVARPASG]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CVARPASG] OFF
END
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_CSTVND] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_CSTVND]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_CSTVND];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_CSTVND] FROM [MYDB].[dbo].[LG_CSTVND]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CSTVND] ON
INSERT INTO MYDB.[dbo].[LG_CSTVND]
(LOGICALREF
,[ACTIVE]
,[CARDTYPE]
,[CODE]
,[TITLE]
,[SPECODE]
,[CYPHCODE]
,[ADDR1]
,[ADDR2]
,[CITY]
,[COUNTRY]
,[ORIGIN]
,[POSTCODE]
,[TELNR1]
,[TELNR2]
,[FAXNR]
,[CLANGUAGE]
,[CCURRENCY]
,[WEBURL]
,[CUSTCAT]
,[GROUPORG]
,[PARENTORG]
,[TEXTINC]
,[SITEID]
,[ORGLOGICREF]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[PRIMARYCONTREF]
,[COUNTRYCODE]
,[CITYCODE]
,[TOWNCODE]
,[TOWN]
,[DISTRICTCODE]
,[DISTRICT]
,[RECSTATUS]
,[RIVALFIRM]
,[TELCODES1]
,[TELCODES2]
,[FAXCODE]
,[GUID])
SELECT LOGICALREF,
[ACTIVE]
,[CARDTYPE]
,[CODE]
,[TITLE]
,[SPECODE]
,[CYPHCODE]
,[ADDR1]
,[ADDR2]
,[CITY]
,[COUNTRY]
,[ORIGIN]
,[POSTCODE]
,[TELNR1]
,[TELNR2]
,[FAXNR]
,[CLANGUAGE]
,[CCURRENCY]
,[WEBURL]
,[CUSTCAT]
,[GROUPORG]
,[PARENTORG]
,[TEXTINC]
,[SITEID]
,[ORGLOGICREF]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[PRIMARYCONTREF]
,[COUNTRYCODE]
,[CITYCODE]
,[TOWNCODE]
,[TOWN]
,[DISTRICTCODE]
,[DISTRICT]
,[RECSTATUS]
,[RIVALFIRM]
,[TELCODES1]
,[TELCODES2]
,[FAXCODE]
,[GUID] FROM [MYDB].[dbo].[YT_RESTORE_LG_CSTVND]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CSTVND] OFF
END
/* EXEC YT_KONTROL_SATISTEKLIFYONETIMI_TABLOLARI */
Blog of Sem 