# Wonderware Historian SQL Query

**URL:** <https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912>\
**Category:** Ignition\
**Tags:** namedqueries, ignition81\
**Created:** [June 17, 2022, 7:49pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912 "2022-06-17T19:49:24Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jgooding](https://avatars.discourse-cdn.com/v4/letter/j/a88e4f/32.png) [@jgooding](https://forum.inductiveautomation.com/u/jgooding)\
**Post date:** [June 17, 2022, 7:49pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/1 "2022-06-17T19:49:24Z")

</div>

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)

```auto
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.`

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [June 18, 2022, 12:31am UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/2 "2022-06-18T00:31:20Z")

</div>

> [@jgooding](#):
>
> ```auto
> 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
> 
> ```

EDIT

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 18, 2022, 12:52pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/3 "2022-06-18T12:52:22Z")

</div>

> [@jgooding](#):
>
> `AND DateTime <= GetDate()") order by DateTime asc`

Should that be

```auto
AND DateTime <= GetDate() 
order by DateTime asc

```

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

---

<div class="post-metadata">

**Author:** ![jgooding](https://avatars.discourse-cdn.com/v4/letter/j/a88e4f/32.png) [@jgooding](https://forum.inductiveautomation.com/u/jgooding)\
**Post date:** [June 28, 2022, 4:31pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/4 "2022-06-28T16:31:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 28, 2022, 4:57pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/5 "2022-06-28T16:57:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [June 28, 2022, 7:46pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/6 "2022-06-28T19:46:56Z")

</div>

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](https://cdn.logic-control.com/docs/aveva/historian/HistorianRetrieval.pdf).

---

<div class="post-metadata">

**Author:** ![jgooding](https://avatars.discourse-cdn.com/v4/letter/j/a88e4f/32.png) [@jgooding](https://forum.inductiveautomation.com/u/jgooding)\
**Post date:** [July 1, 2022, 1:36pm UTC](https://forum.inductiveautomation.com/t/wonderware-historian-sql-query/60912/7 "2022-07-01T13:36:10Z")

</div>

First of All I want to say thank you to @kcollins and @paul-griffith 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.

```auto
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

```