Skip to main content
B
bdeshmukh1-Visitor
1-Visitor
July 23, 2018
Question

Thingworx SQL Query output different.

  • July 23, 2018
  • 1 reply
  • 1816 views

Hi All,

 

I am running a EXEC Stored Procedure statement using a SQL Service to get an output from SQL and then process that data to generate a Report.

But the output table obtained when Run from Thingworx is different, compared to when the same EXEC query is run from SQL. This in turn is generating incorrect data in report. 

Whereas the Number of rows exported is same in both the tables, but the data in few columns do not match. I checked the Start Time and End time of both the table are matching, which might have been the cause for different set of data, as the Thingworx instance and SQL instance are in different time zones.

Does anyone have any idea why the output is different and how can I make sure the output of the execution is same despite where we are executing it from.

 

Any help would be very appreciating.

Thanks in Advance.

 

Regards,

BhushanSQL

1 reply

S
supandey5-Regular Member
5-Regular Member
July 25, 2018

Hi @bdeshmukh I assume you are referring to the external MS SQL instance? Can you share how the data time filter is passed to your service when you execute the service from Mashup or directly on the Composer?

 

What is the exact difference that you see when you compare the results? Any possibility to share screenshot may be?

B
bdeshmukh1-VisitorAuthor
1-Visitor
July 30, 2018

Hi @supandey,

 

Yes, I am referring to an external MS SQL Instance. The DateTime I pass to the SQL Execute query is in "yyyy-mm-dd hh:mm:ss" format. But I have seen no differences in the time values in the returned table.

The Stored Procedure I run returns in all 22 columns. The Number of rows in output are similar to when ran at the SQL instance. But the data for 2 columns are not similar.

1st is about the Unit Description which is not so critical.
2nd mismatch column is a Status column, which returns 1/0, value of this column is different when ran from TWx and SQL instances.
The total of this Status Column is always higher at TWx instance, as compared to SQL instance. Meaning, there are always extra "1" values when ran at TWx, whereas those should be "0".

 

I apologize for not sharing any screenshot, as I cannot.

 

Can you help me with this.

 

Thanks in Advance.

Bhushan

Terms & ConditionsAccessibility statement