Access a Linked Server

Before you begin

Configure a linked server and options using Enterprise Manager, as described in Configuring the Historian OLE DB provider as a Linked Server.

About this task

This topic describes how to access the OLE DB provider as a linked server in an SQL server using the following methods:

OPENQUERY: This is the recommended method of accessing data by means of a linked server. To use this method, you must first configure a linked server definition. You can then use that linked server name in the OPENQUERY command.
Four-Part Name Syntax: To use this method, you must first configure a linked server definition. You can then use that linked server name in the four-part name syntax.
OPENROWSET and OPENDATASOURCE: These methods are considered adhoc methods of accessing data. They are recommended only for infrequently accessed data. When using either method, you must specify the data source, username, and password in each query instead of configuring it once in a linked server definition. If you want to limit the number of users to a defined set of servers and usernames, you can disable all methods of adhoc access by selecting the Disallow Adhoc Accesses option in the Provider Options window.
Note: You cannot use OPENQUERY to access the ihTrend table. Use four-part name syntax to access the ihTrend table.

Procedure

To fetch a list of Historian tags, run the following query:
```
SELECT * FROM OPENQUERY(iHist,'SELECT * FROM ihTags')
```

To fetch tag values from Historian, use the following example code:

SELECT TagName, TimeStamp, Value, Quality FROM OPENQUERY (iHist,'
SET
StartTime=yesterday-12Day, EndTime=Today, IntervalMilliseconds=1Hour, SamplingMode=Calculated, CalculationMode=Maximum
SELECT * FROM ihRawData WHERE TagName LIKE *simulation00001')

To access the ihTrend table from a linked server, run the following query:
```
SELECT * FROM iHist...[SELECT timestamp, *.value FROM ihTrend]
```
Although the four-part name syntax works with all tables, it is only necessary to use it with the ihTrend table, because the ihTrend table does not work with OPENQUERY.
To use OPENROWSET with an SQL query, use the following example code:
```
SELECT * FROM OPENROWSET('ihOLEDB.iHistorian.1', 
'MY_SERVER';'';'','SET starttime="2002-01-30 10:00:00", endtime="2002
```
Note: This example uses double quotes around date and time because single quotes do not work inside the overall single-quoted query. It is important for you to use double quotes in this scenario.

To access a table, use the following example code:

SELECT * FROM OPENDATASOURCE('iHOLEDB.iHistorian.1', 'Data Source=MY_SERVER')...ihTags

To use OPENDATASOURCE with an SQL query and security, use the following example code:

SELECT * FROM OPENDATASOURCE('iHOLEDB.iHistorian.1', 
'Data Source=MY_SERVER;User ID=user1;Password=thepassword')...[SE

To join Historian data with iFIX data logged with AlarmODBC, use the following example code, which determines the last date and time a specific analog tag was raised as an alarm. The date and time are then used to collect the data from the previous hour leading up to the alarm. You can use this example to determin if the value spiked into the 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)

To access linked server data using a stored procedure, use the following example code, which interfaces with the alarm's 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 one hour leading up to the time the alarm occurred.

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

To execute a stored procedure, use the following example code:

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

When you create the stored procedure in Enterprise Manager, include the following 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