Skip to main content
R
rrosenlof1-Visitor
1-Visitor
August 14, 2019
Solved

"Value" SQL keyword giving errors.

  • August 14, 2019
  • 1 reply
  • 1418 views

Hi all,

 

I have a query where one of the columns I'm selected is named "Value", so in my query it appears as:

SELECT .... PM.Value

 

It appears as if ThingWorx/SQL is treating this as a reserved keyword of some sort, nothing I do will allow me to have the word "Value" in SELECT statement. It always fails and returns the error:

Unable to Invoke Service <ServiceName> on <ThingName> : The connection is closed.

 
I have tried aliasing the column name:
PM.Value [Othername]
PM.Value AS [Othername]
 
I have tried creating an input parameter string, setting it to "[Value]" and then putting it into my SQL statement as <<inputparam>>.
 
Microsoft SQL Server Management Studio handles the query completely fine, no issues at all.
 
Any help would be greatly appreciated.
 
Thanks.
 
 
 
Best answer by rrosenlof

Ahhh, I found out my own issue...

 

Turns out the data type of the "Value" column was SQL_VARIANT, I needed to CAST it as a VARCHAR in this case.

 

SELECT ... CAST(PM.Value AS VARCHAR) ColName

 

Thanks.

1 reply

R
rrosenlof1-VisitorAuthorAnswer
1-Visitor
August 15, 2019

Ahhh, I found out my own issue...

 

Turns out the data type of the "Value" column was SQL_VARIANT, I needed to CAST it as a VARCHAR in this case.

 

SELECT ... CAST(PM.Value AS VARCHAR) ColName

 

Thanks.

Terms & ConditionsCookie settingsAccessibility statement