Advanced Linked Server Examples

The following examples display some advanced SQL queries that use the linked server capability in Microsoft SQL Server.

Example 1: Join Historian Data With iFIX Alarms Logged With AlarmODBC

The following example determines the last time and date a specific analog tag went into alarm. Then, the date and time of the alarm is used to collect the data from the previous hour leading up to the alarm. You can use this example to determine if the value spiked into alarm or slowly approached the alarm limit.

declare @var1 as varchar(300)
declare @iHistServer as varchar(10)
declare @Tagname as varchar(40)
declare @HistTagname as varchar(50)
declare @AlarmStatus as varchar(10)
declare @Node as varchar(8)
declare @StartDt as varchar(30)
declare @EndDt as varchar(30)
declare @queryDt as varchar(30)
SET @iHistServer = 'iHistMY_SERVER'
SET @Node = 'MY_SCADA'
SET @Tagname = 'Simulation00001'
SET @HistTagname = 'MY_SERVER.' + @Tagname
SET @AlarmStatus = 'HIHI'
SET @queryDt= DATEADD(day, -1, CURRENT_TIMESTAMP)
SET @EndDt = (SELECT TOP 1 DateTimeLast FROM AlarmODBC WHERE AlarmStatus = @AlarmStatus AND Node = @Node and Tagname =
SET @StartDt = DATEADD(hour, -1, @EndDt)
set @var1 = 'SELECT * FROM OPENQUERY 
('+ @iHistServer +',''SET StartTime="'+ @StartDt +'", EndTime="'+ @Enddt +'"
SELECT Tagname, TimeStamp, Value, Quality FROM ihRawData WHERE TagName = '+ @HistTagname +''')' exec (@var1)

Example 2: Access Linked Server Data With a Stored Procedure

A stored procedure can insulate callers from the details of linked server processing and can improve maintainability by localizing the linked server code.

This example procedure interfaces with the alarm ODBC table to get the last alarm time for a specified tag in the past 24 hours. It then uses this time to retrieve data for the tag from 1 hour before to the time the alarm occurred via a linked server to the Historian server.

The input parameters are the linked Historian server name, tag name, alarm status, and SCADA node name the alarm was created on. This example uses a sim tag in the Historian database rather than setting up a collector to an iFIX SCADA node. An iFIX tag name would probably need to be concatenated with the node and field (node.tagname.fieldname).

This is the command used to execute the stored procedure:

EXEC alarmhist 'iHistMY_SERVER', 'simulation00001', 'HIHI', 'MY_SCADA'

The first time you create the procedure in Enterprise Manager, you must include the following two lines before the create procedure command to avoid an error:

SET ANSI_NULLS ON
GO
(@iHistServer varchar(10),
@Tagname varchar(40),
@AlarmStatus varchar(10),
@Node varchar(8))
AS
declare @var1 as varchar(400)
declare @HistTagname as varchar(50)
declare @StartDt as varchar(30)
declare @EndDt as varchar(30)
declare @queryDt as varchar(30)
declare @count as int
declare @CalculationMode as varchar(20)
SET @HistTagname = 'MY_SERVER.' + @Tagname
SET @queryDt= DATEADD(day, -1, CURRENT_TIMESTAMP)
SET @count = (SELECT COUNT(*) FROM AlarmODBC WHERE AlarmStatus = @AlarmStatus AND Node = @Node AND Tagname = @Tagname
If @count > 0
BEGIN
If @AlarmStatus = 'HIHI' or @AlarmStatus = 'HI'
BEGIN
SET @CalculationMode = 'Maximum'
END
ELSE
BEGIN
SET @CalculationMode = 'Minimum'
END
SET @EndDt = (SELECT TOP 1 DateTimeLast FROM AlarmODBC WHERE AlarmStatus = @AlarmStatus AND Node = @Node AND Tagname =
SET @StartDt = DATEADD(hour, -1, @EndDt)
SET @var1 = 'SELECT * FROM OPENQUERY
('+ @iHistServer +',''SET StartTime="'+ @StartDt +'",
EndTime="'+ @EndDt +'", IntervalMilliseconds=60000,
SamplingMode=Calculated,CalculationMode='+ @CalculationMode +'
SELECT Tagname, TimeStamp, Value, Quality FROM ihRawData WHERE TagName = '+ @HistTagname +''')'
print (@var1)
exec (@var1)
END 
GO