SQL Server Case Sensitive Input Error

[dulrich14]

Bug report

When running sql server input against a case sensitive instance the query errors out.

System info:

SQL Server Collation: Latin1_General_BIN (Case sensitive)
Telegraf Version: Telegraf v1.4.0
OS: Windows

Steps to reproduce:

1. Add a connection to a case sensitive sql server
2. Run telegraf

Expected behavior:

No Errors

Actual behavior:

2017-08-01T19:46:00Z I! Invalid column name 'Latch'.
2017-08-01T19:46:00Z I! Invalid column name 'Lock'.
2017-08-01T19:46:00Z I! Invalid column name 'Network'.
2017-08-01T19:46:00Z I! Invalid column name 'Service broker'.
2017-08-01T19:46:00Z I! Invalid column name 'Memory'.
2017-08-01T19:46:00Z I! Invalid column name 'Buffer'.
2017-08-01T19:46:00Z I! Invalid column name 'XEvent'.
2017-08-01T19:46:00Z I! Invalid column name 'Other'.
2017-08-01T19:46:00Z I! Invalid column name 'Latch'.
2017-08-01T19:46:00Z I! Invalid column name 'Lock'.
2017-08-01T19:46:00Z I! Invalid column name 'Network'.
2017-08-01T19:46:00Z I! Invalid column name 'Service broker'.
2017-08-01T19:46:00Z I! Invalid column name 'Memory'.
2017-08-01T19:46:00Z I! Invalid column name 'Buffer'.
2017-08-01T19:46:00Z I! Invalid column name 'XEvent'.
2017-08-01T19:46:00Z I! Invalid column name 'Other'.

Additional info:

Query #9 Collecting wait stats: When the categories are inserted into the temp @w tables they are all caps. When referenced later on they are regular case.

EX Inserted as: ('LATCH_EX' , 'LATCH') , ('LATCH_KP' , 'LATCH')
Then later referenced as
, [Latch] = SUM([Latch])
, [Lock] = SUM([Lock])
, [Network] = SUM([Network])

Proposal:

Change the following query

I have attached a working version of the full query since it is too long to post. I do not know how to rebuild the source to test.

SELECT
---- measurement
measurement = 'Wait time (ms)'
---- tags
, servername= REPLACE(@@ServerName, '', ':')
, type = 'Wait stats'
---- values
, [I/O] = SUM([I/O])
, [Latch] = SUM([LATCH])
, [Lock] = SUM([LOCK])
, [Network] = SUM([NETWORK])
, [Service broker] = SUM([SERVICE BROKER])
, [Memory] = SUM(MEMORY)
, [Buffer] = SUM([BUFFER])
, [CLR] = SUM([CLR])
, [SQLOS] = SUM([SQLOS])
, [XEvent] = SUM([XEVENT])
, [Other] = SUM([OTHER])
, [Total] = SUM([I/O]+[LATCH]+[LOCK]+[NETWORK]+[SERVICE BROKER]+[MEMORY]+[BUFFER]+[CLR]+[XEVENT]+[SQLOS]+[OTHER])
FROM
(
SELECT
[I/O] = ISNULL([I/O] , 0)
, [MEMORY] = ISNULL([MEMORY] , 0)
, [BUFFER] = ISNULL([BUFFER] , 0)
, [LATCH] = ISNULL([LATCH] , 0)
, [LOCK] = ISNULL([LOCK] , 0)
, [NETWORK] = ISNULL([NETWORK] , 0)
, [SERVICE BROKER] = ISNULL([SERVICE BROKER] , 0)
, [CLR] = ISNULL([CLR] , 0)
, [XEVENT] = ISNULL([XEVENT] , 0)
, [SQLOS] = ISNULL([SQLOS] , 0)
, [OTHER] = ISNULL([OTHER] , 0)
FROM @W5 as P
PIVOT
(
SUM(WaitTimeInMs)
FOR WaitCategory IN ([I/O], [LATCH], [LOCK], [NETWORK], [SERVICE BROKER], [MEMORY], [BUFFER], [CLR], [XEVENT], [SQLOS], [OTHER])
) AS PivotTable
) as T

UNION ALL

SELECT
---- measurement
measurement = 'Wait tasks'
---- tags
, server_name= REPLACE(@@ServerName, '', ':')
, type = 'Wait stats'
---- values
, [I/O] = SUM([I/O])
, [Latch] = SUM([LATCH])
, [Lock] = SUM([LOCK])
, [Network] = SUM([NETWORK])
, [Service broker] = SUM([SERVICE BROKER])
, [Memory] = SUM(MEMORY)
, [Buffer] = SUM([BUFFER])
, [CLR] = SUM([CLR])
, [SQLOS] = SUM([SQLOS])
, [XEvent] = SUM(XEVENT)
, [Other] = SUM(OTHER)
, [Total] = SUM([I/O]+[LATCH]+[LOCK]+[NETWORK]+[SERVICE BROKER]+[MEMORY]+[BUFFER]+[CLR]+[XEVENT]+[SQLOS]+[OTHER])
FROM
(
SELECT
[I/O] = ISNULL([I/O] , 0)
, [MEMORY] = ISNULL([MEMORY] , 0)
, [BUFFER] = ISNULL([BUFFER] , 0)
, [LATCH] = ISNULL([LATCH] , 0)
, [LOCK] = ISNULL([LOCK] , 0)
, [NETWORK] = ISNULL([NETWORK] , 0)
, [SERVICE BROKER] = ISNULL([SERVICE BROKER] , 0)
, [CLR] = ISNULL([CLR] , 0)
, [XEVENT] = ISNULL([XEVENT] , 0)
, [SQLOS] = ISNULL([SQLOS] , 0)
, [OTHER] = ISNULL([OTHER] , 0)
FROM @W5 as P
PIVOT
(
SUM(WaitTaskCount)
FOR WaitCategory IN ([I/O], [LATCH], [LOCK], [NETWORK], [SERVICE BROKER], [MEMORY], [BUFFER], [CLR], [XEVENT], [SQLOS], [OTHER])
) AS PivotTable
) as T;

Wait_time_stats.txt

[danielnelson]

@dulrich14 Can you create a pull request?

[valerian1978]

Is there any pull request for that ?
I 'm facing the same issue

[danielnelson]

@valerian1978 There isn't a pr, I'll create one next week from the bug report and if you can test we try to include it in 1.4.2.

[valerian1978]

Thank you very much for your answer and your action.
I'm testing it, I'll put a reply in appropriate section.