sql-serverformattingquoted-identifierwonderware

Simple SQL - using format inside openquery

I have the following query, using MS SQL Server in a Wonderware (Industrial HMI) Historian application. The keywords beginning with 'ww' are Wonderware specific, wwResolution is getting the results every 24 hours:

SET QUOTED_IDENTIFIER OFF

SELECT * FROM OPENQUERY(INSQL,
"SELECT DateTime = convert(nvarchar, DateTime, 101) + '  23:59:59.000',
[BarRoom_GASMETER.ACC], [DELAQ_GASMETER.ACC]
FROM WideHistory
WHERE wwRetrievalMode = 'Cyclic'
AND wwResolution = 86400000
AND wwVersion = 'Latest'
AND DateTime >= DateAdd(wk,-1,GetDate())
AND DateTime <= GetDate()")'

I want to try and format the query, so I do this:

format([BarRoom_GASMETER.ACC],'###,###,###,###,###.##') as 'Bar Room'

but I get 'Error Occurred'. Any suggestions on how I should be using format in this case? Thanks in advance for the answers. Added snapshot, you will have to save it locally to read it:enter image description here

I think it has something to do with the way 'Quoted Identifier' is being handled.

Solution

  • The issue seems to that that the FORMAT function came out in SQL Server 2012 and you are on SQL Server 2008 which does not have it.

    Although, if I submit a query via OPENQUERY specifying a non-existant function, I get:

    Msg 195, Level 15, State 10, Line 1
    'bob' is not a recognized built-in function name.

    But I also have no problems running the following (on SQL Server 2012):

    SET QUOTED_IDENTIFIER OFF;
SELECT *
FROM OPENQUERY([LOCAL],
               "SELECT *, FORMAT(object_id,'#,#.#') FROM master.sys.objects;");