Dataset Attribute Naming Rules for SQL Column Generation

In the application, when users create Datasets, each dataset has Attributes. These user-provided Attributes are automatically converted into SQL Server column names. To ensure reliable table creation and prevent SQL errors, attribute names must follow SQL Server identifier rules. This document describes the allowed and restricted characters, sanitization behavior, and examples based on typical user input.

How Attributes Become SQL Columns

When a user creates a Dataset and defines its Attributes, each Attribute name is programmatically mapped to a corresponding SQL column in the table backing the Dataset. Therefore, any invalid characters in Attribute names may directly cause SQL errors during table creation or schema updates.

SQL Server Rules for Column Names

Column names must follow the following rules:

• Must be 1–128 characters long.

• Can contain only letters (A–Z, a–z), digits (0–9), and underscores (_).

• Cannot start with a number.

• Cannot contain spaces or special characters unless delimited with brackets (not supported in this application).

• Cannot use SQL reserved keywords.

Example of Invalid Attribute Names

User enters the following Attribute:

TEST SIDE DT°C (MIN)

This Attribute contains invalid characters for SQL column creation, including spaces, the degree symbol (°), and parentheses. Since the application converts Attributes directly into SQL columns, these characters cannot be stored as-is.

How the Application Sanitizes Attribute Names

To ensure the Attribute name becomes a valid SQL column, the system sanitizes user input by applying the following transformations:

• Spaces → replaced with underscores (_)

• Special characters → removed or replaced with underscores

• Unicode symbols (°, ±, µ, etc.) → removed

• Parentheses → removed

• Multiple separators → collapsed into a single underscore

The resulting sanitized name is:

TEST_SIDE_DT_C_MIN

This sanitized name is SQL-safe and can be used internally when creating the Dataset's physical table.

Recommended Naming Patterns for Attributes

To avoid unexpected renaming or sanitization, users should follow these guidelines:

• Use letters, digits, and underscores only.

• Avoid spaces and special characters.

• Avoid using symbols such as °, %, &, (, ), /, etc.

• Prefer readable formats like TEST_SIDE_DT_MIN or inlet_temperature.

Validation Rule Applied by the Application

Before saving an Attribute name, the system validates it using the following pattern. If invalid characters are present, the name will be sanitized automatically:

^[A-Za-z_][A-Za-z0-9_]*$

User-Facing Error/Warning Message

The following message is displayed when an Attribute contains invalid characters:

Attribute Name is invalid. It must start with a letter or underscore and contain only letters, digits, or underscores.
Procedure to Correct Dataset Attribute Names Containing Special Characters

If dataset attributes were previously created using special characters, follow the steps below to correct the data and restore normal functionality.

1. Correct the Attribute Definition in Project Administrator

   1. Open AVEVA P&ID Project Administration Program and navigate to the DataSets page (see User Defined Attributes Configuration Category).

   2. Select the relevant dataset from the list.

   3. Identify and select the attribute whose name contains special characters.

   4. Click Delete and save the changes.

   5. Recreate the attribute using valid naming conventions, then save the updates.

      

2. Clean and Update P&ID Reports Configuration

   6. Open AVEVA P&ID Reports.

   7. Clear existing data by using both the Clear Raw Database and the Clear Database options.

      

   8. Open the Options dialog and select the required table from list on the Table Columns tab.

      

   9. Navigate to the column name that contains special characters using the up/down arrows.

   10. Select the column and click Delete .

   11. Save the changes and exit the AVEVA P&ID Reports application.

3. Synchronize Drawings in the P&ID Application

   12. Open the main AVEVA P&ID application and load the required drawing(s).

   13. Commit the drawing or open the Synchronization module (see Export Data to a P&ID Reports Database for details).

   14. Select the applicable drawings, then synchronize and consolidate them.

After completing these steps, users should be able to modify values for the newly created dataset attributes without issues.