"Value" SQL keyword giving errors.

[rrosenlof]

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.

[rrosenlof · Accepted solution]

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.