cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

Thingworx SQL Query output different.

bdeshmukh
8-Gravel
8-Gravel
‎Jul 23, 2018 10:34 AM
‎Jul 23, 2018 10:34 AM

Thingworx SQL Query output different.

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

Labels:
0 Kudos
Notify Moderator
2 REPLIES 2
supandey
19-Tanzanite
19-Tanzanite
(To:bdeshmukh)
‎Jul 25, 2018 05:28 AM
‎Jul 25, 2018 05:28 AM

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?

0 Kudos
Notify Moderator
bdeshmukh
8-Gravel
8-Gravel
(To:supandey)
‎Jul 30, 2018 02:25 AM
‎Jul 30, 2018 02:25 AM

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

0 Kudos
Notify Moderator
Top Tags