MSSQL
MSSQL (Microsoft SQL Server) is Microsoft's enterprise relational database management system supported in Pirivision's Port module.
Ideal for accessing SQL Server databases via T-SQL queries, common in Windows-based factory environments, ERP systems (SAP, Dynamics), and SCADA infrastructures.
Strengths
| Feature | Description |
|---|---|
| Windows Ecosystem | Deep integration with Active Directory and Windows Server |
| T-SQL Power | Advanced queries via stored procedures, triggers, and window functions |
| ERP Integration | The preferred database for SAP, Microsoft Dynamics, and similar ERP systems |
| MES & SCADA Compatibility | Common with industrial systems like Wonderware, Ignition, Inductive Automation |
| SQL Server Agent | Built-in agent for scheduled and maintenance jobs |
| Always On / Mirroring | High availability and disaster recovery options |
| SSMS Support | Easy management and query development with SQL Server Management Studio |
What You Can Do in Pirivision
| Action | Description |
|---|---|
| T-SQL Query | Retrieve data with SELECT, JOIN, WHERE, HAVING, CTE in Compass |
| Time Series | Trend and range queries via a DATETIME column |
| Stored Procedure Results | Bring existing SP output into Pirivision |
| Cross-Table | Combine multiple tables with JOIN |
| Filtered KPI | Filter by plant, line, or shift with WHERE |
| Aggregation | Summary metrics via AVG, SUM, COUNT, MIN, MAX |
When Is It Used?
- ERP Systems: SQL Server backends of ERPs like SAP HANA, Microsoft Dynamics, Epicor
- SCADA Historian: Access to SQL Server via Wonderware Historian, Ignition, OSIsoft PI
- MES Integration: Access to production orders, shift, and OEE data
- Quality Management: Systems where test and measurement results live in MSSQL
- Reporting: Direct connection to source databases of SSRS or Excel-based reports
Connection Requirements
| Information | Required | Default |
|---|---|---|
| Host | Yes | — |
| Port | Yes | 1433 |
| Database Name | Yes | — |
| Username (SQL Auth) | Yes | — |
| Password | Yes | — |
Authentication Mode
The Pirivision MSSQL connection works in SQL Server Authentication mode (username + password). Windows Authentication (Integrated Security) is not supported in this version.
Guides
| Guide | Description |
|---|---|
| Add MSSQL Data Source | Create a new MSSQL connection in the Port module |
| Edit MSSQL Data Source | Update an existing connection |
| Move Data Source | Move to a different folder |
| Duplicate Data Source | Copy with the same settings |
| Delete Data Source | Remove the data source |
Next Step
After adding an MSSQL data source, create a T-SQL query in the Compass module.