DECLARE @cSyBaseSQL nvarchar(4000) DECLARE @cSQL nvarchar(4000) declare @tLastUpdate datetime = (SELECT COALESCE(MAX(Created), Cast(Cast(getDate() as Date) as DateTime)) From dbo.EventsLog) select (@tLastUpdate) declare @cSource varchar(10) = (SELECT cast(PK as varchar(10)) FROM dbo.Sources WHERE Source = 'SomeSource') SET @cSyBaseSQL = ' SELECT CS.equip_id as equip_id, CS.created As created_llt, CS.set_flag set_flag, str(CS.event + (256 * set_flag)) as event_id FROM syBaseStatus CS JOIN syBaseCodes SC on CS.event + (256 * CS.set_flag) = SC.event where CS.created >= "' + convert(char(19), @tLastUpdate, 20) + '" ' -- This next line I put and remove for testing and it does not seem to make a difference -- order by 1, 2 asc SET @cSQL = ' INSERT INTO dbo.EventsLog ( OperationalDay, fkDevices, Created, fkEventDefinitions, fkSources, setFlag ) SELECT dbo.getOperationDate(Source.created_llt) as OperationDay, Devices.PK as fkDevices, Source.created_llt, ED.PK as fkEventDefinitions, ' + @cSource + ' as fkSources, Source.set_flag FROM OpenQuery(SYBASEDB, ''' + @cSyBaseSQL + ''') Source JOIN dbo.Devices ON Source.Equip_ID = Devices.EquipID and Devices.fkSources = ' + @cSource + ' JOIN dbo.EventDefinitions ED ON ED.EventID = Source.Event_id and ED.fkSources = ' + @cSource + ' ORDER By Source.created_llt ' EXEC (@cSQL)dbo.EventsLog has an insert trigger like this
/****** Object: Trigger [dbo].[InsertFaults] Script Date: 09/28/2009 11:54:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: RAN02 -- Create date: 2009-09-26 -- Description: Trigger to insert a record in the FaultsLog table when the Event being logged is a Fault (the flag isFault of EventsDefinition is True) -- ============================================= ALTER TRIGGER [dbo].[InsertFaults] ON [dbo].[EventsLog] AFTER INSERT AS BEGIN insert into dbo.FaultsLog ( fkFaultLogStatuses, fkEventsLog, fkFaultLogModes, Title, [Description], fkLogTypes, fkSources, fkDevices, fkStations, Created, fkContactGroups, fkFaultsLog ) select (select PK from FaultLogStatuses where status = case i.SetFlag when 1 then 'Open' else 'Closed' end), i.PK, (select PK from FaultLogModes where Mode = 'Auto'), ED.[Description], 'This is an automated entry', (select PK from LogTypes where LogType = case i.SetFlag when 1 then 'Creation' else 'Status Change' end), i.fkSources, i.fkDevices, (select Stations.PK from Devices join Stations on Stations.PK = Devices.fkStations where Devices.PK = i.fkDevices), getDate(), ED.fkContactGroups, case i.SetFlag when 1 then null else (dbo.getClearedEventFaultsLogPK(dbo.getClearedEvent(ED.PK), i.fkDevices)) end from inserted i join dbo.EventDefinitions ED on i.fkEventDefinitions = ED.PK and ED.isFault = 1 order by i.Created ENDThe only field of interest is fkFaultsLog which should be the parent record of an opened fault (when it is being closed which is determined by i.SetFlag = 0) the two stored prcedures getClearedEventFaultsLogPK (Horrible name I came up on a Sunday when I was very tired) and getClearedEvent() both seem to work fine when I ran them from outside, but they always return null from the trigger, that is why I thought it was a problem with the commit