DSC and SQL Server problem, I need help.

[Terry_S]

I am working on a project that is using the DSC module in LabView 2019.  In my project I have created my I/O server (Modbus/TCP) and bound variables.  Each variable has data logging enabled to a Citadel 5.  I deploy all and I can see the  data in the distributed manager and in a data view within MAX.  My client however would like to share the data across a network.  Therefore I thought that I could send the data to a SQL server using the relational database option that is available in the DSC:Database setup.  However after creating my connection to the SQL server and testing it (using the "Test Connection" button), nothing appears in the SQL server. Of course I have deployed again after making these changes.

Unfortunately this is a very poorly supported and documented product, but I need to make this work.  So far NI has not been able to help me, so I am hoping someone in the community can give me some assistance.  Question I have are;

1. Are the tables automatically created by DSC in the SQL server or do I need to create them?

2. Can the data be logged to the Citadel and Relational Database at the same time, meaning the box is checked in both tabs?

3. Is there some type of permissions that I need to set in order to log the data to the SQL server?

4.  Is this just not possible and I have been sold some snake oil?

I know I could write my own routines to write to the database, but I have a large number of "Tags"  was sold on the fact that the I/O server would look after this for me.

[RUBEN_LEON]

you solve this?

I need log data on SQL server relational database. But i try a lot of VI and configs, and not good.

[Terry_S]

Hi Ruben,

This problem had to do with letting DSC automatically log the tags created in my modbus server to an SQL database.  I never did get this to work.

However, using the database toolkit, I created my own connection to the SQL database and sent the data to a table in the database.  My client wanted to log the data at present intervals, logging within the server only logs data when a value changes, so this approached allowed me to create what my client required.  This was done for a client, so I don't have any examples of what I did, but the examples that come with the database toolkit are very helpful.

The steps I took where,

-Create SQL database and required tables/fields

-In labview create connection to the database, here I choose to create a dsn file connection.  I have the best luck using this approach.

-Then I used the DB Tools insert data.vi to insert the data into the appropriate fields in the table.

I found that naming if tags, table and fields was something to be aware of.  The labview vi's do not like names that have spaces and special characters in them.  Keep them simple.

hope this helps.

[RUBEN_LEON]

Good morning terry

I got it to work.

- In DSC Relational Database: I use first from Default Database.

- In SQL SERVER: After generating some test data. I entered the CITADEL instance, and copied: the Database, and the nicitadel user.
Then in the desired instance, I created the Database and the user.

- In DSC Relational Database: I use the connectionstring (third) method. And put de nicitadel user and password.

It works fine, even with a latency of 10 milliseconds.

Thanks for answering.

At least we left ideas on how to solve it

[lio123]

Great questions!

I am also facing the same problem of lack of documentation in changing to a rational database.

I hope that a national engineer reads this forum and can answer your questions.

Cheers,

Lior

[RUBEN_LEON]

In SQL SERVER i solve it. 

I publish the SQL's to create the tables structure.

For use the database is necesary a user with, minimum, dbreader and dbwriter, roles.

CREATE TABLE [dbo].[NI_ALARM_EVENTS](
[alarm_id] [uniqueidentifier] NOT NULL,
[priority] [int] NULL,
[process] [nvarchar](255) NULL,
[trace_url] [nvarchar](1023) NULL,
[is_alarm] [bit] NULL,
[area] [nvarchar](127) NULL,
[description] [nvarchar](511) NULL,
[set_user] [nvarchar](127) NULL,
[set_point] [float] NULL,
[set_value] [float] NULL,
[set_time] [datetime] NULL,
[set_tz_offset] [int] NULL,
[set_tz_name] [nvarchar](127) NULL,
[ack_user] [nvarchar](127) NULL,
[ack_comment] [nvarchar](511) NULL,
[ack_time] [datetime] NULL,
[ack_tz_offset] [int] NULL,
[ack_tz_name] [nvarchar](127) NULL,
[auto_ack] [bit] NULL,
[clear_user] [nvarchar](127) NULL,
[clear_time] [datetime] NULL,
[clear_tz_offset] [int] NULL,
[clear_tz_name] [nvarchar](127) NULL,
PRIMARY KEY CLUSTERED
(
[alarm_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NI_VARIABLE_BOOLEAN](
[traceurl] [nvarchar](1023) NULL,
[value] [bit] NULL,
[timestamp] [datetime] NULL,
[quality] [bigint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NI_VARIABLE_NUMERIC](
[traceurl] [nvarchar](1023) NULL,
[value] [float] NULL,
[timestamp] [datetime] NULL,
[quality] [bigint] NULL
) ON [PRIMARY]
GO

[lio123]

Thanks Leon!

I created tables in MySQL according to your create database statement. So far I have only checked the numeric table. For some reason, the shared variable engine saves the timestamps in the database in local time and not UTC. Did you run into this problem in your relational database?

Cheers,

Lior

[RUBEN_LEON]

For my database, local time suited me perfectly.
For the problem you say, maybe you can do an automatic procedure in MySQL to change the time.
I have done something similar to pass the values ​​from the "NUMERIC" table to a personal table.