Wonderware Historian SQL Query

Ignition
,
1

I am hoping that someone can help me out. I have a valid connection to the database and it appears to be working. When I run the following in SSMS, I get the results I would expect. (As the user that is authenticated against database)

SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [TAG1], [TAG2]
 FROM WideHistory
 WHERE wwRetrievalMode = 'Cyclic'
 AND wwResolution = 1000
 AND wwQualityRule = 'Extended'
 AND wwVersion = 'Latest'
 AND DateTime >= DateAdd(hh,-1,GetDate())
 AND DateTime <= GetDate()") order by DateTime asc

When I run it using the query browser, I get the following error:

Error running query: SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [TAG1], [TAG2] FROM WideHistory WHERE wwRetrievalMode = 'Cyclic' AND wwResolution = 1000 AND wwQualityRule = 'Extended' AND wwVersion = 'Latest' AND DateTime >= DateAdd(hh,-1,GetDate()) AND DateTime = GetDate()") order by DateTime asc The identifier that starts with 'SELECT DateTime, [TAG1], [TAG2] FROM WideHistory WHERE wwRetrievalMode = 'Cyclic' AND wwResolu' is too long. Maximum length is 128.

2

EDIT

SELECT DateTime, [TAG1], [TAG2]
 FROM WideHistory
 WHERE wwRetrievalMode = 'Cyclic'
 AND wwResolution = 1000
 AND wwQualityRule = 'Extended'
 AND wwVersion = 'Latest'
 AND DateTime >= DateAdd(hh,-1,GetDate())
 AND DateTime <= GetDate()
order by DateTime asc
3

Should that be

AND DateTime <= GetDate() 
order by DateTime asc

I think you forgot to remove the closing ") from the original query.

1 Like
4

It looks like it is giving an error of only 128 characters are allowed in the subquery? Is that a limit in JDC file?

5

The “identifier is too long” limitation is coming from the MSSQL server or JDBC driver. I assume there’s some reason you’re using the OPENQUERY syntax rather than just writing your query directly, but if you just write your query directly, there’s no such restrictive limitation.

6

The OPENQUERY syntax is one method for using the custom OLE DB provider that must be invoked (via the linked server definitions) to service those queries. The “tables” (i.e. Runtime.dbo.History or ...WideHistory) for WW Historian queries don’t exist as standard tables in the database.

You might also have luck using a fully qualified reference to the extension tables via the linked server definition. See page 15 of this doc on WW Historian Retrieval.

7

First of All I want to say thank you to @kcollins1 and @PGriffith for leading me to the right answer. The issue is with the double quotes and single quotes. I am not sure how it worked originally but it did. Then it stopped working. The double quotes can only have 128 characters in it. Once I changed the variables to double quotes and the overall string to single quotes it worked. Thanks to all for the help.

SELECT * FROM OPENQUERY(INSQL, 'SELECT DateTime, SysTimeSec, SysTimeMin
 FROM WideHistory
 WHERE wwRetrievalMode = "Cyclic"
 AND wwResolution = 1000
 AND wwQualityRule = "Extended"
 AND wwVersion = "Latest"
 AND DateTime >= DateAdd(hh,-1,GetDate())
 AND DateTime <= GetDate()') order by DateTime asc
1 Like