[SOLVED] Selecting a column with period in the column name SQL Server

Selecting a column with period in the column name SQL Server

I am linked to a Proficy Historian that allows periods in the column names. Because the data is stored in a non DBMS format I can not use openquery to get the data because there is no set schema to the tables. So I must use four part name syntax to get the data. This example works:

SELECT * FROM iHist...[SELECT * FROM ihTrend]

but this fails with Incorrect syntax near '.'.

SELECT * FROM iHist...[SELECT [SERVER.pid_astatus[07][0].F_CV.Value] FROM ihTrend]

where SERVER.pid_astatus[07][0].F_CV.Value is the name of the column

This fails as well with Incorrect syntax near the keyword 'from'.

SELECT * FROM 
    iHist...[SELECT [SERVER.pid_astatus[[07]][[0]].F_CV.Value] from ihTrend]`

Any ideas on how I can make SQL Server see this as a column?

EDIT:

Martins suggestion of the right brackets to escape the brackets work only on the outside of the sql call

SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value] FROM iHist...[SELECT * FROM ihTrend]

However it does not work inside Incorrect syntax near the keyword 'from'.

SELECT * FROM iHist...[SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value] FROM ihTrend]

EDIT

SELECT * FROM iHist...[SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value]] FROM ihTrend]

I had to escape the column escape :)

Solution

You only need to escape these ]

[pid_astatus[07]][0]].F_CV.Value]

This works for me

CREATE TABLE #t(
    [pid_astatus[07]][0]].F_CV.Value] int
) 

SELECT [pid_astatus[07]][0]].F_CV.Value]
FROM #t