Moving Recorder to MS SQL - Install Dependencies

rvdbijl · January 22, 2021, 8:35pm

Answering my own question after digging through the forum further. Appears to be a bug in HA – Workaround is to create the states table manually through the SQL Management Studio with this query:

CREATE TABLE states (
state_id INTEGER NOT NULL IDENTITY(1,1),
domain VARCHAR(64) NULL,
entity_id VARCHAR(255) NULL,
state VARCHAR(255) NULL,
attributes VARCHAR(max) NULL,
event_id INTEGER NULL,
last_changed DATETIME NULL,
last_updated DATETIME NULL,
created DATETIME NULL,
old_state_id INTEGER NULL,
PRIMARY KEY (state_id),
FOREIGN KEY(event_id) REFERENCES events (event_id) ON DELETE CASCADE,
FOREIGN KEY(old_state_id) REFERENCES states (state_id) ON DELETE NO ACTION
)

Just did that, and running fine now!

TyrelVanNiekerk · January 30, 2021, 7:15pm

rvdbijl:

CREATE TABLE states (
state_id INTEGER NOT NULL IDENTITY(1,1),
domain VARCHAR(64) NULL,
entity_id VARCHAR(255) NULL,
state VARCHAR(255) NULL,
attributes VARCHAR(max) NULL,
event_id INTEGER NULL,
last_changed DATETIME NULL,
last_updated DATETIME NULL,
created DATETIME NULL,
old_state_id INTEGER NULL,
PRIMARY KEY (state_id),
FOREIGN KEY(event_id) REFERENCES events (event_id) ON DELETE CASCADE,
FOREIGN KEY(old_state_id) REFERENCES states (state_id) ON DELETE NO ACTION
)

Fantastic!!! Thanks. That worked for me.

rich_foley · January 30, 2021, 7:49pm

Did you make sure whatever user you set up for HA had the “db_owner” role?

TyrelVanNiekerk · February 4, 2021, 1:29am

Yes. All the other tables created correctly and after adding that table manually, it started writing data to the db. Seems like the code to create the table is not correctly taking SQL Server into account.