Help with Triggered SQL Expression Item

Hello all, a little introduction and background, I’m an engineer working for a third party software company that has a company using Ignition (version 7.5.5-beta6) as a customer right now. I’m trying to set up a SQL Server table that our software would use to pull some data in from our client’s SCADA system but am having problems. I’ve read through some of the documentation and searched the board but did not quite find what I need. If I missed it I apologize, please redirect me to the appropriate address.

Originally one of the engineers at the site set everything up using a couple transaction groups (one for each machine I’m dealing with), but he created the output table so that each SCADA tag has its own column, and has a machine ID column as well that I need to cross-reference when I pull data. The table is also not clustered on either the time stamp or the machine ID. I worry this will become increasingly slow as time passes and more data and columns are added (there will be a need to trend a lot of historical data in our software, so data retrieval speed is important).

What I would like instead is to have a tag list table, where each tag name and machine combo has a unique ID (I have created this table already). Then I would like the data table to be skinny and long, with only a couple fields: record ID, Tag ID, timestamp, value, quality. I would then make the clustered key for the table the Tag ID. This way I can query the tag list table for all the information I need on a given tag, and using the Tag ID I can quickly query all the data for a given tag out, even if there is a lot of data in the future.

So the engineer gave me the credentials to the Designer to see if I could get it how I want it, however I know very little about Ignition itself. If I could just get a trigger to write the tag ID every time it inserts a new record then I’d be in great shape. I also notice that the engineer who set it up originally has an Expression Item called Mach_ID that writes that into the table every time the trigger fires, and that the expression items have a SQL option. Since I have control over my tag list table I figured I could do something like this:

USE COVBatch
SELECT [TagId] FROM PVTagList 
WHERE [TagName] = Name --This is the OPC tag name, I'm hoping it looks like 'Dryer_TEMP_SPT_Z1'
AND  [MachineId] = 5

There will be a group for each machine, so I can hard code that I believe. I was hoping that if I select one item from the tables, that would be the value of the expression item, but it only returns an evaluation error. Nothing I try seems to work, so I worry that I misunderstand how to get this to return a value, or if it it meant to return a value at all (perhaps it is only for executing inserts, updates, deletes, etc…?). The other problem I suppose is that right now all the tag values are inserted as one row, where I would like them inserted each as an individual row.

I do not know if this is a simple request or very difficult. I could probably make a trigger that does this in SQL server itself, but it is an old server and I’m trying to avoid taxing it too heavily, so a solution using the designer would be preferable. This is probably a lot of explanation for a fairly basic question, so thanks in advance for any help, links, or whatnot.

I’m sure that, Dan (or someone from his team at Sherwin-Williams) can help you with this. We’re all recuperating from seeing each other face-to-face.

The historical logging in Ignition stores data in the “narrow” format tables you mention.

However it may well not be suitable because you need to trigger the logging based on something (e.g. a separate tag change) rather than just logging data at regular intervals.

How many tags are involved?
If not too many then you could have one Historical Transaction Group per tag and get them all to write into the same table, effectively hard-coding the TagID within the transaction group.

Niemers,

You can use an Expression Tag to run a SELECT query if you want. The evaluation error is probably being caused by something silly like a data type mismatch between the Expression Item and the returned data or not including quotes around the tag name. You should be able to find out more about the error if you look at the ‘Events’ tab below the Triggered Expression Items.