Show value from SQL Server

Third party integrations
1

Hello,

I would like show some data values in HA coming from SQL Server table.
Can you help me please how can I do this?

I try with the SQL integration but after fill everything it says: “Unknown error occurred”.

What I have?

  • Installed HA .
  • Installed SQL server with named instance (MYPC\SQL) with enabled remote connection

The connection string what I try:

mssql+pyodbc://sa:[email protected]\SQL:1433/MYHOME?charset=utf8&driver=FreeTDS

Can somebody help with a step by step guide? (I am beginner in HA).

Thank you!

2

I’m afraid you’re a bit in the realms of wishful thinking, here.

  1. pyodbc is not available in the HA image
  2. Even if it was, you would have to add the MS SQL driver first
  3. HA only officially supports Sqlite/MySQL/MariaDB/PostgreSQL
3

Can you help me please how can I install pyodbc and the driver? If I good understand it should be installed into the HA virtual machine?
SQL Server connection can be working on a non-officially way?

Anyway there are no sulution in 2022 to connect to a simple SQL Server database? That is wierd for me.

A MySql or Sqlite tutorial would be also good if there are no chance with SQL Server. Thank you!

4

No clue, and surely unsupported, so not a good idea if you cannot self-support it.

Again, not sure where you got that MS SQL server is a “simple” database server…

What are you trying to achieve?
Where does the data you want to inject into HA comes from?

5

Ok, thank you!

Not the database system is simple, but also the client connection. I “coming” from .NET world and a client connection and get data is a 5 minute task.

I collect measurement values from self-developed logger applications with lot of devices. All data stored in SQL server but I have no visual interface. So I would like show my logged data values in a visual interface like HA.

Since I don’t know HA well enough, I’m wondering what kind of custom data source can I implement?

  • Can I read data from Web API (I can create web api that reads data from SQL Server)? So can I use GET or POST request to show value in HA?
  • Can I read from simulated Modbus Server device?
  • Can I read from simulated OPC Server?
  • Can I read from dedicated file (CSV) or FTP etc?
  • Any other solution to show custom data in HA?

Thank you very much!

6

Of course. But you are far from .NET in the HA/Linux world…

If you have the choice, just go with MariaDB or PostgreSQL to make your live easier.
Those also have ODBC drivers, so, assuming your apps are written in .NET, should be painless.

Yes: RESTful - Home Assistant
For the other: Probably, but I have no experience

7

Thank you I will try the Web API example, I think this will be the easiest solution.
Changing the database system is a bigger work (data migration, republish every software, create backup plans etc…)

8

Ok I think I am near to finish with an example.

I have this code in sensors.yaml:

- platform: rest
  resource: https://xy.azurewebsites.net/api/demo
  method: GET
  name: "Demo WebApi"
  value_template: "{{ myJson1.randomValue }}"
  scan_interval: 5

The configuration file:

# Loads default set of integrations. Do not remove.
default_config:

# Text to speech
tts:
  - platform: google_translate

automation: !include automations.yaml
script: !include scripts.yaml
scene: !include scenes.yaml
sensor: !include sensors.yaml

The template:

{## Imitate available variables: ##}
{% set myJson1 = {
	"randomValue": 54
} %}

{{ (myJson1.randomValue ) }}

After that I restart HA.
The sensor appear in the list, but the value is ‘unknown’ (The webapi produces a random int value).

Can you help me please what is missing?

Thank you!

9

Ah its working now, I should use value_json instead of myJson1.

1 Like