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”.