query="mssql_connections" -> Scan error on column index 0, name "db": unsupported Scan, storing driver.Value type <nil> into type *string`

[iulian-olteanu]

Hello,

We have the following error:

I0113 15:51:22.498632 9256 promhttp.go:38] Error gathering metrics: [from Gatherer #1] [, collector="mssql_standard", query="mssql_connections"] scanning of query result failed: sql: Scan error on column index 0, name "db": unsupported Scan, storing driver.Value type <nil> into type *string

The metric definition is

- metric_name: mssql_connections
    type: gauge
    help: 'Number of active connections.'
    key_labels:
      - db
    values: [count]
    query: |
      SELECT DB_NAME(sp.dbid) AS db, COUNT(sp.spid) AS count
      FROM sys.sysprocesses sp
      GROUP BY DB_NAME(sp.dbid)

Do you know what is causing it ?

[jroboyd]

DB_NAME(sp.dbid) is returning NULL for some reason. stack overflow link

Another way of writing it would be:

SELECT db.name AS db, COUNT(sp.spid) AS count
      FROM sys.sysprocesses sp
	  INNER JOIN sys.databases db ON db.database_id = sp.dbid
      GROUP BY db.name