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 documentation 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 documentation 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.
