History Database Connection

I am trying to get a database connection working so I might set up tag history.

I am using SQL Server. ( I am not a database person)

The connection name is MSSQLServer.
The JDBC driver is Microsoft SQLServer.
The database is local, so the connect URL is jdbc:sqlserver://localhost\SQLExpress:1433.
The user name is Ignition.
Extra Connection Properties are databaseName=msign.
Enabled is checked.
Translator = MSSQL.
Include Schema in Table Name is checked.
Connection Pooling, Connection Testing, and Connection Initialization are default.

Database Connections show the connection as valid.

The SQL Server Management has:
Login = Ignition + password.
Server Roles has ‘public’ checked.
User mapping has the msign database checked. The roles are db datareader, db datawriter, and public checked.
Securables has (My computer name)\SQLEXPRESS as the server.
Explicit permissions are ‘connect SQL’ -Grantor sa, ‘Create any database’ – Grantor sa.
Status Grant to database engine, Login Enabled.
Msign database has User and Login name = Ignition. Default schema dbo.
Owned Schemas all unchecked.
Role Member db_datareader and db_datawriter checked.
Securables is empty.
Extended Properties Database msign, Collation SQL_Latin1_General_CP1_CI_AS

History Datasource history Provider status is Running.

The tag is an OPC Integer that has random numbers written by the PLC. I can watch the values change from the Tag Browser in Ignition Designer.

History Enabled = true
Storage Provider = MSSQLServer
Deadband Style = Auto
Deadband Mode = Absolute
Historical Deadband = 15.0
Sample Mode = Periodic
Max Time Between Samples = 10 Seconds
Sample Rate = 5 Seconds

I can run the SQL Query ‘Select Record From dbo.Table_1’ from the Database Query Browser and it returns some stuff from the ‘Record’ column in the msign database

I have two tags, ‘Query Tag’ and ‘Int_1’. Both point to the same register in the PLC. ‘Query Tag’ does not have history enabled, ‘Int_1’ does have history enabled.

I can drag and drop ‘Query Tag’ on an Easy Chart and everyone is happy.
If I drag and drop the ‘Int_1’ on the same chart, I get:

Exception: Error running query:
Tag Density Histogram Query
Range: Tue Jan 14 08:00:00 EST 2020->Tue Jan 14 09:59:59 EST 2020
prov:default:/tag:Query Tag
On: New Main Window.Root Container.Easy Chart

caused by GatewayException: Error executing tag history query. Please verify that the connection is available, and that tag history is being stored to it.

caused by Exception: Error executing tag history query. Please verify that the connection is available, and that tag history is being stored to it.

Ignition v8.0.6 (b2019111216)
Java: Azul Systems, Inc. 11.0.4

Read and Write and enabled. Any suggestions as to where to look?
I can see no tables in the database that are created by Ignition. Does the table have a specific name?

I’m not a SQL Server expert, but your symptoms suggest that the Ignition user in the DB doesn’t have the privileges needed to create the historian tables.

I agree. The connection seems to be working. Not being a SQL expert either, I am at a bit of a loss figuring out what permissions the tag needs.

Not the tags. The Ignition user in the DB. When I create DB instances in other brands, I usually just make the Ignition user be the owner from the start, so it has all necessary permissions.

Solved for the moment. I gave Ignition the permission to ‘Alter any database’ and everyone seems happy again. Perhaps that is overkill, but that is an issue for another day.

It doesn’t need alter ANY database.
Just make the login you created the Owner of the database you want to use for historian.

1 Like

I shall try that indeed.



above link will tech you :how to install and connect MYsql database . i finish it before some days .

the page will tech you : how to install another type database in you computer . see bottom of this page.