Tag History SQL - Multiple Tables?

Fired up my first Ignition app today and so far so good - device communication, graphics alarms and tag logging all good. So…

I’m logging data to an MS SQL DB and now that I’ve had it running I can see that it looks to be creating a table in my DB for every month’s worth of data (probably). Ignition has created 2 tables ‘dbo.sqlt_data_1_2021_12’ and ‘dbo.sqlt_data_1_2022_01’ I can see data in there which is great. Outside of Ignition if I wanted to query data across these two databases (or more if it keeps creating one each month) - how would I do it, does Ignition provide a view or stored procedure in there that joins the databases ?

i.e. I want to do a statement such as SELECT floatvalue FROM ‘what goes here !’ WHERE t_stamp > ‘1 Dec 2020 00:00:00’ AND t_stamp < ‘4 Jan 2021 23:59:00’

Is there something magical that takes care of joing the tables for me (cough…ahem…FactoryTalk dumps it all in one table for me and WinCC has a ‘provider’ which does all the work for me). ??

Thanks for any help.

Partitioning and pruning are configurable for each history provider. You can make it one table and do your own pruning. Otherwise, don’t access the SQL externally. The structure, while relatively stable over the years, is not guaranteed to not change. Use WebDev or a custom module to expose an API, which would use Ignition’s history queries on the caller’s behalf.

For tables that you really must share externally, consider using transaction groups (from the SQL Bridge module) or scripting your database inserts.

Hi Phil

Thanks for your help…this data does need to be accessed externally for a MES system, if this system was never upgraded to a newer Ignition version and remained at 7.9 (if it was to be upgraded they would probably speak to us first) if I disable partitioning as a quick and admittedly dirty workaround would the table that it’s already created ‘dbo.sqlt_data_1_2022_01’ remain forever effectively - would there ever be a chance of it being renamed by the system ? From this point onwards would it just keep pumping data into that table ??

If you want to keep the partitions as is, one month, you can write queries that will stretch across multiple tables. Ignition is doing it, you can as well. Last time I did something like this I used a prepared statement.

I don’t know. I suspect it will create sqlt_data_2 and start putting everything there.