FactoryTalk View SE Data logging in MS-SQL
Published on May18, 2025 | Category: sqlMicrosoft SQL Server (MS-SQL) is a powerful relational database management system (RDBMS) developed by Microsoft. It is designed to store, retrieve, and manage large volumes of data efficiently and securely. MS-SQL can be installed on both Windows and Linux operating systems and is accessible through a wide range of client tools, graphical interfaces (like SQL Server Management Studio), and programming APIs. In industrial automation, particularly in PLC and SCADA applications, MS-SQL plays a vital role in data management. It is commonly used to store process data such as alarm logs, event logs, historical trends, tag values, and critical process variables. This stored data can be used for real-time monitoring, analysis, reporting, diagnostics, and audit purposes. MS-SQL provides robust features such as:
- Data integration: Seamless integration with other industrial and enterprise applications.
- Data analysis: Advanced analytics and querying capabilities using SQL and T-SQL.
- Data reporting: Integration with tools like Power BI, SSRS (SQL Server Reporting Services) for detailed visualization.
- Security: User authentication, role-based access, data encryption, and secure access controls.
- Scalability: Can handle small to very large databases, making it suitable for enterprise-level SCADA systems.
- High availability: Features like replication, clustering, and Always On availability groups ensure minimal downtime.
- Express Edition: Free and ideal for learning, development, and lightweight production applications.
- Standard Edition: Suitable for small to medium-sized businesses with essential database features.
- Enterprise Edition: Full-featured edition for mission-critical, high-performance applications.
- Developer Edition: Free edition with all Enterprise features but licensed for development and testing only.
FactoryTalk View SE ODBC Database Storage
ODBC, or Open Database Connectivity, is a standardized interface developed by Microsoft that allows Windows-based applications to access data from different database management systems such as MySQL, Oracle, and Microsoft SQL Server. It enables connectivity through a set of standard APIs and supports functions like connecting to databases, executing SQL queries, retrieving data, and managing transactions. ODBC drivers are available in both 32-bit and 64-bit versions, and are widely used in various industries for database integration and communication.
Create Database In MS-SQL Server for FactoryTalk View Data Logging
Before proceeding, download and install the MS-SQL Server Express edition. This edition is free and ideal for small-scale development, desktop, and SCADA data logging applications.
To create a new database in SQL Server:
- Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
- In the Object Explorer, right-click on the "Databases" folder and choose "New Database".
- Enter a name for your database (e.g., plcblogdatalog) and click "OK".
- Alternatively, use this SQL query:
CREATE DATABASE plcblogdatalog;
Connecting SQL Server to FactoryTalk View SE for Data Logging
In FactoryTalk View Studio, open the Data Log folder in the Explorer panel. Right-click on Data Log Models and choose New. In the setup tab of the Data Log Model editor, select ODBC Data Source as the storage format to connect with SQL Server.
FactoryTalk View ODBC Table Names
When selecting ODBC as the storage format, three types of tables are available:
- Float Table: Logs numeric (analog and digital) values from your tags.
- String Table: Optionally used to log tag values of string type.
- Tag Table: Maintains tag names and indices for efficient data referencing.
The default table names are TagTable, FloatTable, and StringTable. It is recommended to keep these names unchanged for simplicity.
How to Log Data to ODBC Data Sources in FactoryTalk View SE
You can store data in either an existing or new ODBC Data Source. Follow these steps to configure a new one:
- Click the browse button next to the ODBC Data Source field in the setup tab.
- Select the System Data Source tab and click New.
- Choose System Data Source and proceed.
- Select SQL Server as the driver and click Next.
- Enter a name and description for your DSN. Choose the appropriate server (manually if not listed).
- Select your preferred authentication method (Windows or SQL Server).
- Select the previously created database (plcblogdatalog) and finish the setup.
- Test the connection to verify it is working.
- Select your DSN in FactoryTalk View Studio and click OK.
- Click Create Tables to generate tables in your SQL database. A message will confirm success.
Configure ODBC Backup Path in FactoryTalk View Studio
To prevent data loss when the SQL server is unreachable, you can configure a backup path for your ODBC data source. Select Enable ODBC Backup Path and specify an absolute or relative path to store temporary data files.
Auto Purge Old Records in ODBC Database
In the File Management tab, you can define rules to automatically delete old records after a specified duration. This helps in managing disk space and ensuring your database remains optimized.
Define Logging Triggers in FactoryTalk View Studio
In the Log Triggers tab, configure when data should be logged:
- Periodically: At fixed intervals (e.g., every 10 seconds).
- On Change: When a tag value changes.
- On Demand: When a specific event occurs, such as executing a command.
How to Add Tags to the ODBC Data Source
Navigate to the Tags In Model tab to browse and select the tags you want to log. Each data log model can handle up to 10,000 tags of any data type.
Save ODBC Data Source Configuration
After all configurations are complete, click OK and assign a meaningful name to your Data Log Model. This name will be used in scripts and triggers.
Start Data Logging in FactoryTalk View
You can start data logging using one of the following methods:
- Open the Command Line and type:
DataLogOn 'plcblog' - Right-click the data log model component and select Start.
Stop Data Logging in FactoryTalk View
To stop data logging:
- Open the Command Line and type:
DataLogOff 'plcblog' - Right-click the data log model component and select Stop.
Verify Data Logging in SQL Server
Open Microsoft SQL Server Management Studio (SSMS), connect to your database, and browse the tables. If configured properly, you will find three tables: FloatTable, StringTable, and TagTable.
Float Table Format:
- DateAndTime: Timestamp when the tag value was logged.
- Millitm: Milliseconds of the logging timestamp.
- TagIndex: Numerical reference of the tag (linked to TagTable).
- Val: Logged analog or digital value.
- Status: Communication status. E = Error, S = Stable, U = Uninitialized.
- Marker: Internal tag tracking marker.
Tag Table Format:
- TagName: Name of the SCADA tag.
- TagIndex: Numeric index assigned to each tag.
- TagType: 2 = Analog, 3 = Digital, 4 = String.
- TagDataType: -1 = Integer, 0 = Long, 1 = Float, 2 = String.