Wonderware Historian’s retrieval modes – free technical whitepaper from Wonderware North

Wonderware North released their Tech Tip on Wonderware Historian’s retrieval modes entitles “Wonderware Historian – A Look At Different Data Retrieval Methods”. The documents describes various retrieval modes of Historian in relation to data acquisition and it also compares built-in retrieval modes of Historian with standard Transact SQL aggregate functions.

The document can be found on the Wonderware North website.

Wonderware Historian 10 will be released in February 2010

Wonderware Historian 10 is expected to be generally available in February 2010. It’s going to be the first major release in few years. It will introduce a set of new features included “tier historian” – read more about what’s new here. This release will also introduce support for SQL Server 2008 (32-bit only) and Windows Server 2008.
Wonderware customers that have a valid support agreement will be eligible to receive Historian 10 license for free. Contact Wonderware PacWest if you want to get on support.

Note: Wonderware Historian 10 will only support 32-bit version of SQL Server 2008 installed as a default instance. Named instances of SQL Server 2008 are not supported.

How to retrieve wide-history data from Wonderware Historian (InSQL) using a stored procedure

Below is an example of a stored procedure that will retrieve wide-history data from Wonderware Historian 9.0 (formerly known as IndustrialSQL Server or InSQL):

CREATE PROCEDURE HistoryData
@StartDateTime datetime,
@EndDateTime datetime,
@Tags varchar(500) = ‘[SysDateDay], [SysDateMonth], [SysDateYear]’
–above: @Tags is an optional parameter, value after the equal sign is the default value if @Tags is omitted
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQLString varchar(1000)
SET @SQLString = ‘SET QUOTED_IDENTIFIER OFF ‘
SET @SQLString = @SQLString + ‘SELECT * FROM OPENQUERY(INSQL, ‘
SET @SQLString = @SQLString + + ‘"SELECT DateTime = convert(nvarchar, DateTime, 21), ‘ + @Tags + ‘ ‘
SET @SQLString = @SQLString + ‘FROM WideHistory WHERE wwRetrievalMode = ”Cyclic” AND wwResolution = 1000 AND wwVersion = ”Latest” ‘
SET @SQLString = @SQLString + ‘AND DateTime >= ‘ + CHAR(39) + CAST(@StartDateTime AS varchar(50)) + CHAR(39)
SET @SQLString = @SQLString + ‘ AND DateTime <= ‘ + CHAR(39) + CAST(@EndDateTime AS varchar(50)) + CHAR(39) + ‘")’ EXEC (@SQLString)
END

The procedure accepts 3 parameters:
– Start date/time
– End date/time
– optional comma separated list of tag – this parameter allows the procedure to be run for different sets of tags
To install the procedure just run the script above against your Wonderware Historian’s Runtime database from within MS SQL Server Management Studio 2005 (or MS SQL Server 2000 Enterprise Manager).
Below is an example of how to run the command:

EXEC HistoryData ‘20081027 21:04:00′,’20081027 21:07:00’, ‘SysTimeSec, SysDateDay’

The example below retrieves data for tags SysTimeSec and SysTimeDay between Oct 27th, 2008 9:04pm and 9:07pm. But you can also skip the 3rd parameter and run it this way:

EXEC HistoryData ‘20081027 21:04:00′,’20081027 21:07:00’

In such case the procedure will return data for the list of tags specified as the default value of the @Tags variable (in the example below the default value of the @Tags variable is "SysDateDay, SysDateMonth and SysDateYear".

Wonderware IndustrialSQL (InSQL) Historian Server 9.0 Patch02 is available

Wonderware IndustrialSQL (InSQL) Historian Server 9.0 Patch02 is available. This patch primarily includes support the following Wonderware Application Server 3.1 features:

  • Configure most historization parameters for a tag from within the IDE (e.g. “swinging-door” filters, counter “rollover”, interpolation type, etc.)
  • Time propagation from data in Application Server (already supported from IDAS in Historian 9.0)
  • “Late” data from Application Server (“late” data from IDAS was already supported in Historian 9.0)

This release also includes several hotfixes for Historian 9.0 Patch01 released over the last ~20 months. The release is reflected on Wonderware PacWest’s Product Version page.

Wonderware Tech Note 563 “Configuring Wonderware FSGateway to Access Historian Data from Wonderware Application Server” is available

Wonderware Tech Note 563 “Configuring Wonderware FSGateway to Access Historian Data from Wonderware Application Server” is available.

Wonderware IndustrialSQL Server Historian (InSQL) provides an I/O Server called aahIOSvrSvc. This I/O Server is an interface for clients to access data from the Historian. It uses the SuiteLink protocol. The Historian I/O Server is re-configured with a single topic called tagname. The Historian I/O Server listens for clients that are attempting to establish a connection using the pre-configured topic.

Wonderware FSGateway is a communications protocol converter. It is used to link clients and data sources communicating with different communication protocols such as SuiteLink, DDE, FastDDE, and ArchestrA Message Exchange.

This Tech Note provides the step-by-step procedure to:

  • Configure the FSGateway to communicate with the Historian I/O Server
  • Configure the OPCClient proxy object from the Wonderware Application Server to access data from the Historian / FSGateway OPC Server via OPC protocol.

Click here to review the tech note.

How to retrieve wide-history data from Wonderware Historian using a stored procedure

Below is an example of a stored procedure that will retrieve wide-history data from Wonderware Historian (formerly known as IndustrialSQL Server):

CREATE PROCEDURE HistoryData
@StartDateTime datetime,
@EndDateTime datetime,
@Tags varchar(500) = ‘[SysDateDay], [SysDateMonth], [SysDateYear]’
–above: @Tags is an optional parameter, value after the equal sign is the default value if @Tags is omitted
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQLString varchar(1000)
SET @SQLString = ‘SET QUOTED_IDENTIFIER OFF ‘
SET @SQLString = @SQLString + ‘SELECT * FROM OPENQUERY(INSQL, ‘
SET @SQLString = @SQLString + + ‘"SELECT DateTime = convert(nvarchar, DateTime, 21), ‘ + @Tags + ‘ ‘
SET @SQLString = @SQLString + ‘FROM WideHistory WHERE wwRetrievalMode = ”Cyclic” AND wwResolution = 1000 AND wwVersion = ”Latest” ‘
SET @SQLString = @SQLString + ‘AND DateTime >= ‘ + CHAR(39) + CAST(@StartDateTime AS varchar(50)) + CHAR(39)
SET @SQLString = @SQLString + ‘ AND DateTime <= ‘ + CHAR(39) + CAST(@EndDateTime AS varchar(50)) + CHAR(39) + ‘")’
EXEC (@SQLString)
END

The procedure accepts 3 parameters:
– Start date/time
– End date/time
– optional comma separated list of tag – so that you can run it for different set of tags

To install the procedure just run the script above against your Historian’s Runtime database from within SQL Server Management Studio.

And here’s how you execute the command:

EXEC HistoryData ‘20081027 21:04:00′,’20081027 21:07:00’, ‘SysTimeSec, SysDateDay’

But you can also skip the 3rd parameter and run it this way:

EXEC HistoryData ‘20081027 21:04:00′,’20081027 21:07:00’