The Narrow Format OLEDB interface (Q12160) can be used with Ignition Scada by Inductive Automation. In order for the interface to work you will need to create a database stored procedure for the interface.
Version Tested: 7.8.3 as of 01/24/2017
STORED PROCEDURE:
This stored procedure will combine the last two months of data and the tag table from Ignition Scada.
USE
[Ignition_Test]
GO
/****** Object: StoredProcedure [dbo].[WIMSPREP] Script Date: 1/23/2017 3:42:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Hach Co.>
-- Create date: <01/24/2017>
-- Description: <Stored Procedure for Ignition Scada>
-- =============================================
CREATE PROCEDURE [dbo].[WIMSPREP]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Declare this months table
DECLARE @TableName VarChar(max)
--Declare Last months table
DECLARE @LastMthTable VarChar(max)
--Declare the Month as a two character string
DECLARE @MTH Varchar(2)
--Set the year for the Current Month Table
Set @TableName = CAST(YEAR(GetDate()) as varchar)
--Set the Current Month converted into two character string
SET @MTH = RIGHT('0' + RTRIM(MONTH(GETDATE())), 2)
--Set the entire Current Month Table Name
Set @TableName = 'sqlt_data_1_' + @TableName + '_' + @MTH
--Set the year for Last Month
Set @LastMthTable = CAST(YEAR(DateAdd(month,-1,GetDate())) as varchar)
--Set Last Month converted into two character string
SET @MTH = RIGHT('0' + RTRIM(MONTH(DateAdd(month,-1,GETDATE()))) ,2)
--Set the entire Last Month Table Name
Set @LastMthTable = 'sqlt_data_1_' + @LastMthTable + '_' + @MTH
EXEC
('dr0p table WIMSDATA')
EXEC('SELECT DATEADD(second,'+@LastMthTable+'.t_stamp/1000, CAST(''1970-01-01 00:00:00'' AS datetime)) as ts,'+@LastMthTable+'.tagid, sqlth_te.tagpath,Case WHEN '+@LastMthTable+'.FloatValue IS Null then '+@LastMthTable+'.intValue else '+@LastMthTable+'.floatvalue END curvalue, '+@LastMthTable+'.t_stamp INTO WIMSDATA FROM ' +@LastMthTable +', sqlth_te where ' + @LastMthTable+'.tagid=sqlth_te.id')
EXEC
('INSERT INTO WIMSDATA SELECT DATEADD(second,'+@TableName+'.t_stamp/1000, CAST(''1970-01-01 00:00:00'' AS datetime)) as ts, '+@TableName+'.tagid, sqlth_te.tagpath, Case WHEN '+@TableName+'.FloatValue IS Null then '+@TableName+'.intValue else '+@TableName+'.floatvalue END curvalue, '+@TableName+'.t_stamp FROM ' + @TableName + ',sqlth_te where '+@TableName+'.tagid=sqlth_te.id')
END
NOTE: The First EXEC statement has a zero (0) in the word Drop because KB would not allow the drop statement. When running the stored procedure you must change dr0p to drop.
WIMSDATA table created by the stored procedure:
Depending on how the interface is scheduled the stored procedure must be run prior to each import to esure the data is up to date in the WIMSDATA table.
The best approach is to use an SQL Server Agent/Job. NOTE: this can only be done on any non-express edition of SQL.
In our test the stored procedure took over 2 minutes to run, therefore, it would need to be set to run at least 3 minutes before the interface was set to import.
NOTE: The ts field is converted to a UTC date/time from the t_stamp Ignition Epoch time. In order for the interface to Operate you must check Source Dates are in UTC in the Configuration/Source Configuration/Advanced tab.
Table Mappings:
This what your Table Mapping should look like for the interface Q12160:
Schedule the Stored Procedure to Run:
Create a new job with SQL Server Agent
Name the new Job
Add a Step to the Job, that will run the stored procedure (Steps/New...). The Command is EXEC WIMSPREP. NOTE: Make sure to run it against the Ignition Database by choosing the proper Database is database: drop down.
Schedule the Stored Procedure to run daily (Schedules/New...).
After configuring the Job hit ok and make sure that the Job is now listed under Jobs.