sp3

USE [SZ_OpMon]
GO
/****** Object:  StoredProcedure [dbo].[AATISsp_GetInSQLEventData2]    Script Date: 2014/8/5 10:42:27 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER     PROCEDURE [dbo].[AATISsp_GetInSQLEventData2]
@st NVARCHAR(19)
,@et NVARCHAR(19)
AS
/*************************************************************/
/* Copyright 2004 AATIS, Inc. All Rights Reserved */
/* Author:  John Dado*/
/* Rev 1.0  10/28/2004*/
/*modified to get both integer and discrete lot in progress*/
/*************************************************************/
SET NOCOUNT ON

begin try
--print 'in get sql event'
 DECLARE @sSQL as VARCHAR(8000)
 DECLARE @taglist as VARCHAR(8000)
 DECLARE @Vname as NVARCHAR(80)
 DECLARE @qt as NVARCHAR(1)
 SET @qt = char(39)
 SET @taglist = ''
 --create a list of the keyanalogvariables
 DECLARE csrVnameI CURSOR LOCAL FOR
  (select
   DISTINCT VariableName
  from
   AATISvw_EventVariables a
  Inner Join
   Runtime.dbo.Tag b
  On
   b.TagName = a.variablename
  inner join
   AATIS_ProductionMachines c
  on
   a.MachineKey = c.MachineKey
   and c.MachineType = 'IntegerLotInProgress'
  )
  OPEN csrVnameI
  FETCH NEXT FROM csrVnameI INTO @vname
    WHILE @@FETCH_STATUS = 0
  BEGIN
 --1000 char = appx 40 tags
   set @taglist = @taglist + @qt+ @vname + @qt + ','
   IF LEN(@taglist) > 1000 --XP_DISCRETEHISTORYDELTA DOES NOT HANDLE INFINITE TAGS
   BEGIN
    SET @taglist = dbo.AATISfn_ClearComma(@taglist)
    SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_AnalogHistoryDelta '
    + @qt+@st + @qt
    + ','+@qt+ @et + @qt
    + ',1000' 
    + ',' + @taglist
  --print '[AATISsp_GetInSQLEventData2] ' + @ssql 
    EXECUTE (@sSQL)
    SET @taglist = ''
   END
   FETCH NEXT FROM csrVnameI INTO @vname
 --select @vname
  END
 CLOSE csrVnameI
 DEALLOCATE csrVnameI
 IF LEN(@taglist) > 0 --GET THE REMAINING TAGS IF ANY
 BEGIN
  SET @taglist = dbo.AATISfn_ClearComma(@taglist)
  SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_AnalogHistoryDelta '
  + @qt+@st + @qt
  + ','+@qt+ @et + @qt
  + ',1000' 
  + ',' + @taglist
  --print @ssql 
  EXECUTE (@sSQL)
 END

SET @taglist = '' --now get the discrete lot in progress machine events
 
 
 DECLARE csrVnameD CURSOR LOCAL FOR
  (select
   DISTINCT VariableName
  from
   AATISvw_EventVariables a
  Inner Join
   Runtime.dbo.Tag b
  On
   b.TagName = a.variablename
  inner join
   AATIS_ProductionMachines c
  on
   a.MachineKey = c.MachineKey
   and c.MachineType = 'DiscreteLotInProgress'
  )
  OPEN csrVnameD
  FETCH NEXT FROM csrVnameD INTO @vname
    WHILE @@FETCH_STATUS = 0
  BEGIN
 --1000 char = appx 40 tags
   set @taglist = @taglist + @qt+ @vname + @qt + ','
   IF LEN(@taglist) > 1000 --XP_DISCRETEHISTORYDELTA DOES NOT HANDLE INFINITE TAGS
   BEGIN
    SET @taglist = dbo.AATISfn_ClearComma(@taglist)

   SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_DiscreteHistoryDelta '
   + @qt+@st + @qt
   + ','+@qt+ @et + @qt
   + ',1000' 
   + ',' + @taglist
  
    EXECUTE (@sSQL)
    SET @taglist = ''
   END
   FETCH NEXT FROM csrVnameD INTO @vname
 --select @vname
  END
 CLOSE csrVnameD
 DEALLOCATE csrVnameD
 IF LEN(@taglist) > 0 --GET THE REMAINING TAGS IF ANY
 BEGIN
  SET @taglist = dbo.AATISfn_ClearComma(@taglist)

   SET @sSQL = 'insert into aatis_tmpAnalogHist exec master..xp_DiscreteHistoryDelta '
   + @qt+@st + @qt
   + ','+@qt+ @et + @qt
   + ',1000' 
   + ',' + @taglist
  
  EXECUTE (@sSQL)
 END
 
end try

begin catch
 exec AATISsp_ErrorLog
end catch