Simple SQL - using format inside openquery

2
votes

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.

3
sql-serverformattingquoted-identifierwonderware
What version of SQL Server is it? Maybe not QUOTED_IDENTIFIER as I am able to turn that off as well and submit a similar query using FORMAT(field, '###,##.#') without a problem. – Solomon Rutzky
And can you test by changing the # to maybe 1 to see if it works, just to see if it is the pound-sign itself that is the issue. If it is, it might need to be escaped somehow. – Solomon Rutzky
2008. I just discovered DDE is broken on that box, so there are core issues with the OS. The '1' trick was a great idea, but no luck. – Bill J.

3 Answers

2
votes

I found a solution. It is not elegant, but it woks for Server 2008 and Wonderware Historinan. It will return data at midnight for the previous month, formatted to 2 decimal places.

SET QUOTED_IDENTIFIER OFF    
SELECT datetime , [BarRoom_GASMETER.ACC] = convert(decimal(38,2),             
[BarRoom_GASMETER.ACC])    
FROM OPENQUERY(INSQL, "SELECT Datetime , [BarRoom_GASMETER.ACC],     
FROM WideHistory    
WHERE [SysTimeHour] = 23    
AND [SysTimeMin] = 59    
AND wwRetrievalMode = 'Cyclic'    
AND wwResolution = 60000    
AND wwVersion = 'Latest'    
AND DateTime >= DateAdd(mm,-1,GetDate())    
AND DateTime <= getdate()    
")

Thanks to @KHeaney and @srutzky

0
votes

You should not need all those extra # signs in your formatting. Try just using this

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

Edit

For your other problem I found this forum post: http://www.plctalk.net/qanda/showthread.php?t=52677 It seems like it is possible that you problem is due to reaching a 128 character limit. This could no longer be the case though as the post is over a year old.

If this is the case, one of the posters mentions that to overcome this limitation you need to swap the single and double quotes in your query. This means that your new query should look like this:

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

Also removed the last single quote from your example as I did not see what it closed.

0
votes

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;");