How to write MS SQL queries?

Grafana version: 7.5.7
Operating System: Windows 10

Hi, I am a new Grafana user. Still learning the ropes.

Trying to create a dashboard with an MS SQL database linked as a datasource. However, I am stuck at writing the SQL queries.

I find the sample query very confusing:

SELECT
$__timeEpoch(<time_column>),
  <value column> as value,
  <series name column> as metric
FROM
  <table name>
WHERE
  $__timeFilter(time_column)
ORDER BY
  <time_column> ASC

Tried reading up on Microsoft SQL Server | Grafana Labs and somehow figured out the the words starting with $ sign are macros.

A colleague at work said I do not need to use these sample queries. So I simply put “Select * from objectSensor”. Got an error message, “mssql: Invalid object name ‘objectSensor’.”

Query Inspector shows:

### mssql: Incorrect syntax near '<'.

Object
status:400
statusText:"Bad Request"
data:Object
results:Object
message:"mssql: Incorrect syntax near '<'."
config:Object
url:"api/tsdb/query"
method:"POST"
data:Object
retry:0
headers:Object
hideFromInspector:false
message:"mssql: Incorrect syntax near '<'."

Clicking “Show Help” shows some text on Time Series which I cannot copy and paste here.

Tried a few of the queries given in Microsoft SQL Server | Grafana Labs and one of my own. Got different errors:

Example 1:

 CREATE USER grafanareader WITH PASSWORD 'pass1234'
 GRANT SELECT ON dbo.YourTable3 TO grafanareader

Error message 1:

mssql: Incorrect syntax near 'Pass1234'.

Example 2:

USE master
IF NOT EXISTS (
   SELECT name
   FROM sys.databases
   WHERE name = N'grafana'
)
CREATE DATABASE [grafana]

Error message 2:

mssql: CREATE DATABASE permission denied in database 'master'.

Example 3:

USE grafana
CREATE TABLE [mssql_types] (
  c_bit bit, c_tinyint tinyint, c_smallint smallint, c_int int, c_bigint bigint, c_money money, c_smallmoney smallmoney, c_numeric numeric(10,5),
  c_real real, c_decimal decimal(10,2), c_float float,
  c_char char(10), c_varchar varchar(10), c_text text,
  c_nchar nchar(12), c_nvarchar nvarchar(12), c_ntext ntext,
  c_datetime datetime,  c_datetime2 datetime2, c_smalldatetime smalldatetime, c_date date, c_time time, c_datetimeoffset datetimeoffset
)

INSERT INTO [mssql_types]
SELECT
  1, 5, 20020, 980300, 1420070400, '$20000.15', '£2.15', 12345.12,
  1.11, 2.22, 3.33,
  'char10', 'varchar10', 'text',
  N'☺nchar12☺', N'☺nvarchar12☺', N'☺text☺',
  GETDATE(), CAST(GETDATE() AS DATETIME2), CAST(GETDATE() AS SMALLDATETIME), CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), SWITCHOFFSET(CAST(GETDATE() AS DATETIMEOFFSET), '-07:00')
Query editor with example query:

Error message 3:

mssql: Database 'grafana' does not exist. Make sure that the name is entered correctly.

Yet I could only put “master” in the Database field and not say, “grafana1” which I have created in my MS SQL Server using SSMS.

I get a Login error:

Login error: mssql: Cannot open database “grafana1” that was requested by the login. Using the user default database “master” instead.

I think you are having 2 different issues…successfully establishing a connection to your MSSQL database, and writing a Grafana-friendly SQL query in Grafana.

Are you sure you got the “Success” message in the Datasources?
image

Hi, @grant2. Ah… Pleased to meet you online, Grant Pinkos. Thank you for responding to my query.

Yes.

Here is a screenshot of my MS SQL settings:

I had some problems with adding the data source earlier. Resolved them by:

  1. Adding an inbound rule in Windows Defender Firewall for TCP port 1433.
  2. Adding the TCP port 1433 using the MS SQL Server Configuration Manager found in Windows’ Computer Management.