SET Statements and Variables Examples

If you do not change any variables using the SET statement or a WHERE clause in your SELECT statement, the Historian OLE DB provider assumes default session variables. For instance, if you do not specify a start and end time for your collected data, the data output from a SELECT statement would be the last two hours prior to execution of the query.

For example, if you want to SELECT all of the messages from the ihMessages table for the last day, you would need to explicitly state that you want the messages from the last day in the query. Otherwise, only the messages from the last two hours are displayed when you run the query, since that is what the default assumes.

SET statement variables persist during a session until changed. You can combine the SET statement on the same line as the SELECT statement. The following examples show sample uses of the SET statement.

Example 1: Perform a Simple SET

SET samplingmode=currentvalue

Example 2: Perform Multiple SETs

SET starttime='14-sep-2001 11:00:00', endtime='14-sep-2001 12:00:00', samplingmode=interpolated, intervalmilliseconds=

Example 3: Prepare for a RawByTime Query

SET starttime='14-sep-2001 11:00:00', endtime='14-sep-2001 12:00:00', samplingmode=rawbytime

Example 4: Prepare for a RawByNumber Query

SET starttime='14-sep-2001 11:00:00', samplingmode=rawbynumber, numberofsamples=10, direction=backward

Example 5: Prepare for One Hour Minimums

SET starttime='15-sep-2001 00:00:00', endtime='16-sep-2001 00:00:00', samplingmode=calculated, intervalmilliseconds=36

Example 6: Prepare for a Filtered Data Query

SET starttime='14-sep-2001 11:00:00', endtime='14-sep-2001 12:00:00', samplingmode=current, filtertag='MY_SERVER.simul

Example 7: Throttle Results with a SET Statement

SET ROWCOUNT = 4
SELECT Tagname FROM ihTags