Use the OPENQUERY function

You can use the linked server name in an OPENQUERY function to retrieve data from an extension table. The OPENQUERY function is required for retrieving from the wide table. For example:

SELECT * FROM OPENQUERY(INSQL, 'SELECT * FROM History

WHERE TagName = "SysTimeSec"

AND DateTime >= "2001-09-12 12:59:00"

AND DateTime <= "2001-09-12 13:00:00"

')

The following example retrieves data from a wide table:

SELECT * FROM OPENQUERY(INSQL, 'SELECT DateTime, SysTimeSec

FROM WideHistory

WHERE DateTime >= "2001-09-12 12:59:00"

AND DateTime <= "2001-09-12 13:00:00"

')

The OPENQUERY portion of the statement is treated as a table by SQL Server, and can also be used in joins, views, and stored procedures. SQL Server sends the quoted statement, unchanged and as a string, to the AVEVA Historian OLE DB provider. Consequently, only the syntax that the AVEVA Historian OLE DB provider can parse is supported. Also, be sure that you do not exceed the 8000 character limit for the statement. Consider the following example:

SELECT * FROM OpenQuery(INSQL, 'XYZ')

where "XYZ" is the statement to pass. You should be sure that the value of "XYZ" is not more than 8000 characters. This limit is most likely to cause a problem if you are querying many tags from a wide table.

Also, you should supply the datetime in an OPENQUERY statement in the following format:

yyyy-mm-dd hh:mm:ss.fff

For example:

2001-01-01 09:00:00.000

You cannot use variables in an OPENQUERY statement. For more information, see -old-Using Variables with the Wide Table.