Randomly missing records when writing to SQL database

[leoRW]

Hello everybody,

I am using a Thing with MSSqlServer template to connect the system to a SQL database. The goal is to log PLC tag (binded to a Remote Thing) values, and store each under its relative database column.

I am using a simple SQL command to write these values into the database:

Spoiler

INSERT INTO *TableName* (Column1,Column2, ..., Column15)

VALUES ([[input1]],[[input2]], ..., [[input15]])

Together with another service linking each input parameter to my remote thing properties:

Spoiler

var params= {

input1: prop1,

input2: prop2,

...

input15: prop15

};

SQLcommand(params);

With this, I am able to ALMOST get what I want. In fact, when looking at what I am actually logging in the SQL database, it looks like this:

Some of the items are consistently well logged, others are only sometimes logged, and my timestamp (which is built concatenating multiple items) gets filled with zeros (or is straight up wrong) when something in the logging process goes wrong. This random failures seem to be only related to some (?) string data types.

The Thingworx application log is filled with the following errors, while nothing unusual pops up in the other logs.

Spoiler

EDIT: I have verified that the "Error executing batch" has nothing to do with my issue, so now I do not have anything reported in the logs.

Any idea what this can be related to?

Cheers to everybody,

Leo

[raluca_edu]

Hi,

First idea coming into my mind is to use a try catch for SQLCommand and try to get any error in the log (e.g. use logger.debug)

Also, how often the data in remote thing is changing? Your service is running sync or async?

Thanks,

Raluca Edu

[leoRW]

Hi raluca,

Thank you for your quick reply!

I am trying to use the try/catch instruction but it doesn't seem to log anything different from before in the Application or Script log. It may be very well the case that I am not using it correctly: (Transaction is the service that is calling the rest of the sequence)

Spoiler

try
{
me.Transaction();
}
    catch(err)
{
    logger.debug("debugMSG");
}

The data in the remote thing has scan rate of 1s. I am sure (from PLC code) that the values I am trying to log stay constant for way more than that.

About sync/async services, I did not know about this capability in TW until you mentioned it, so everything was on sync as per default.

I have tried toggling the async box and things have improved significantly, and only few values are occasionally missed in the log now. However, this is still not enough as the logged values are needed for tracking purposes and there cannot be missing bits there.

Regards,

Leo

[leoRW · Accepted solution]

Update: I have solved the issue.

The culprit was the Cache Method setting in my remote thing, which is by default set to "Cached for specific time". I changed all of them to "Fetch from remote every read" and since my properties stay constant for long enough, the system has no trouble logging the values.