Randomly missing records when writing to SQL database

Question

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 (Highlight to read)
INSERT INTO *TableName* (Column1,Column2, ..., Column15)
VALUES ([[input1]],[[input2]], ..., [[input15]])

INSERT INTO *TableName* (Column1,Column2, ..., Column15)
VALUES ([[input1]],[[input2]], ..., [[input15]])

Together with another service linking each input parameter to my remote thing properties:
Spoiler (Highlight to read)
var params= {
input1: prop1,
input2: prop2,
...
input15: prop15
};
SQLcommand(params);

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 (Highlight to read)
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

leoRW · Accepted Answer

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.

raluca_edu · Answer

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

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded