From my understanding ThingWorx gives you barely any error reporting at all for SQL Commands? Nothing appears in the Script Log and the only error that appears when I execute the service is:
Unable to Invoke Service updateLastRunningValuesToDatabase on TimesheetDB : null
The ThingTemplate of the Thing that connects to the Database with this service on is a 'RemoteDatabase' ThingTemplate.
It is connected to a MSSQL Database.
So I just tested this on a Database thing for MySQL, and the syntax is a bit different, but it does work. Here is the syntax in MySQL: select exists(select * from table_name WHERE field='field_value');
I found this syntax on stack overflow: Usage of MySQL's "IF EXISTS" - Stack Overflow
As far as I can tell, if exists... is valid syntax for MSSQL, so I am not sure what the problem is. You are right in saying that error message is largely unhelpful. I am going to find out if I can get a copy of MSSQL and test that as well.
Not 100% sure why the IF EXISTS is not working. Are you using an ADO or ODBC driver to connect? If so, which one? Occasionally some have funky quirks with syntax. Alternatively you could try the following...
SET NOCOUNT ON
DECLARE @Hold AS STRING;
SET @Hold = '0';
SELECT @Hold = '1' FROM CEN_ThingWorx WHERE Thing='TestThing'
IF (@Hold = '1')
SET StringColumn ='newString'
I am using an OleDB Driver this is the connection string if it helps...
"ConnectionString": "Provider=SQLNCLI11; Server=xxxx;Database=ThingWorx;User ID=xxxx;Pwd=xxxx; Trusted_Connection=false;",