Simple SQL - using format inside openquery

Question

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.

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, Jan 15 '15 at 16:35
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, Jan 15 '15 at 16:44
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., Jan 15 '15 at 16:44

score 2 · Answer 1 · answered Jan 16 '15 at 20:30

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

KHeaney · Answer 2 · 2015-01-15T16:38:06.917

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.

You are right on how I was using hash marks, but I still cannot imbed the format inside the query. The query becomes part of an active factory page that gives the operators daily accumulated gas meter usage (there are 13 of them, I only showed 2). — Bill J., Jan 15 '15 at 15:48
@BillJ. I am, unfortunately, not that familiar with the wonderware suite only sql. Does it still only say "Error Occerred"? Is it possible that your factory page strongly types the result somewhere as a numeric type instead of a string? — KHeaney, Jan 15 '15 at 15:58
Yes, when I try to put it inside the OPENQUERY I get 'Error executing query: Incorrect syntax near #' — Bill J., Jan 15 '15 at 16:12

Solomon Rutzky · Accepted Answer · 2015-01-15T16:54:16.150

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

Simple SQL - using format inside openquery

3 Answers3