Use server-side cursors

Cursors are a very powerful feature of SQL Server. They permit controlled movement through a record set that results from a query.

For in-depth information on cursors, see your Microsoft SQL Server documentation.

The AVEVA Historian OLE DB Provider provides server-side cursors. Cursors can be used to do joins that are not possible in any other way. They can be used to join date/times from any source with date/times in the history tables.

The following query provides an example of using a server-side cursor. This query:

• Fetches all of the events in the EventHistory table.

• Shows a "snapshot" of three tags at the time of each event.

• Shows the event tag and its associated key value.

This query could easily be encapsulated into a stored procedure. The query uses the four-part naming convention.

SET QUOTED_IDENTIFIER OFF

DECLARE @DateValue DateTime

DECLARE @EventTag nvarchar(256)

DECLARE @EventKey int

DECLARE @Qry1 nvarchar(500)

DECLARE @Qry2 nvarchar(500)

DECLARE @Qry3 nvarchar(500)

SELECT @Qry1 = N'SELECT EventTag = @EventTag, EventKey = @EventKey, DateTime, TagName, Value, Quality

FROM History

WHERE TagName IN (N''SysTimeSec'', N''SysTimeMin'', N''SysTimeHour'')

AND DateTime = '''

SELECT @Qry2 = N''''

SELECT @Qry3 = N''

DECLARE Hist_Cursor CURSOR FOR

SELECT DateTime, TagName, EventLogKey

FROM Runtime.dbo.EventHistory

OPEN Hist_Cursor

FETCH NEXT FROM Hist_Cursor INTO @DateValue, @EventTag, @EventKey

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Qry3 = @Qry1 + convert(nvarchar, @DateValue, 121) + @Qry2

--PRINT @Qry3

EXEC sp_executesql @Qry3, N'@EventTag nvarchar(256),

@EventKey int', @EventTag, @EventKey

FETCH NEXT FROM Hist_Cursor INTO @DateValue, @EventTag, @EventKey

END

CLOSE Hist_Cursor

DEALLOCATE Hist_Cursor

The results are:

(3 row(s) affected)

(3 row(s) affected)