'Using a subquery in WonderWare to select all tags

I'm trying to get data from a WonderWare historian database on all tags in the database. There is already a table containing all the tags, so I would like to use a subquery to select all of the tag names. I'm getting an error w.r.t this subquery.

My sql statement:

/******

I want to be able to select:
    - all columns
    - for all tags <-- I'm getting an error here, trying to use a subquery
    - between two time stamps
    - at a specified resolution

******/

SELECT *
FROM
    Runtime.[dbo].AnalogHistory
WHERE
    TagName IN (
        SELECT DISTINCT TagName
        FROM
            Runtime.dbo.Tag
    )
AND
    DateTime >= '2016-01-01 00:00'
AND
    DateTime < '2016-01-01 00:30'
AND
    wwResolution = 5000
AND
    wwRetrievalMode = 'Average'
AND
    wwTimeStampRule = 'Start'

The error I am receiving is the following:

OLE DB provider "INSQL" for linked server "INSQL" returned message "History queries must contain at least one valid tagname".

Which I guess means that it isn't being executed properly.

I've also tried using openquery, but then I get other errors related to the subquery unable to discern 'schema' information about the Tag table:

OLE DB provider "INSQL" for linked server "INSQL" returned message "Failed to retrieve schema information for object 'Tag'".

This was the excerpt changed:

TagName IN ( SELECT * FROM OPENQUERY(INSQL, 
    'SELECT DISTINCT TagName
    FROM
        Runtime.dbo.Tag')
)
sql-serverwonderware


Solution 1:[1]

Historian Server is not a normal SQL Server database. It's historical views get data from the INSQL linked server. INSQL has some special rules that are documented in HistorianConcepts.pdf.

SELECT 
    *
FROM
    Runtime.dbo.Tag t
INNER REMOTE JOIN 
    Runtime.dbo.AnalogHistory h
ON 
    t.TagName = h.TagName
WHERE
    DateTime >= '2016-01-01 00:00'
AND
    DateTime < '2016-01-01 00:30'
AND
    wwResolution = 5000
AND
    wwRetrievalMode = 'Average'
AND
    wwTimeStampRule = 'Start'

Solution 2:[2]

Try:

SELECT DISTINCT aa.TagName FROM (
 SELECT DateTime, TagName, Value
 FROM History
 WHERE DateTime >= '2016-01-01 00:00'
 AND DateTime <= '2016-01-01 00:30'
 AND wwResolution = 5000
 AND wwRetrievalMode = 'Average'
 AND wwTimeStampRule = 'Start'
) aa

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 trailer
Solution 2 Taraz

Related Questions