Skip to content

Create MSSQL Query (T-SQL)

This page explains the process of writing and saving a T-SQL query against an MSSQL data source in the Pirivision Compass module.

T-SQL Differences

MSSQL uses the T-SQL (Transact-SQL) dialect, which differs from standard SQL. Use TOP N instead of LIMIT, GETDATE() instead of NOW(), and DATEADD() / DATEDIFF() for date arithmetic.

Prerequisite — Port Data Source

To create this query, an MSSQL data source must be defined in the Port module. If none exists yet, go to Port and create a connection first.

→ Port: Add MSSQL Data Source

Information Needed Before Creating

The examples on this page use the Demo Factory MSSQL demo_fabrika.vardiya_uretim table and the Demo Factory - MSSQL data source in Port. To generate the same data in your own environment, see Synthetic Data Generation — MSSQL.

Information Description Default Example
Data Source An MSSQL connection added in Port — Demo Factory - MSSQL
Query Name The name shown in the Compass list — mssql_shift_production
T-SQL Statement The T-SQL query to run — SELECT TOP 50 * FROM demo_fabrika.vardiya_uretim WHERE hat_id = {{hat_id}} ORDER BY ts DESC
Target Folder Compass folder Root Demo Factory / Production

1. Open the Compass Module

Click Compass in the left menu.

Compass list

2. Create a New Query

Click the Add New Query button.

3. Pick an MSSQL Data Source

On the Select Your Data Source screen, pick the data source of type MSSQL. Click Next.

Data source selection — MSSQL

4. The SQL Editor Opens

The T-SQL editor screen opens. Tables and columns of the MSSQL database are listed in the left panel.

T-SQL editor — MSSQL connected, table browser on the left

5. Write the T-SQL Query

-- Last 50 production rows — TOP N usage (instead of LIMIT) — Demo Factory
SELECT TOP 50 ts,
       hat_id,
       makine_id,
       vardiya,
       urun_ailesi,
       uretim_adet
FROM   demo_fabrika.vardiya_uretim
WHERE  ts >= DATEADD(day, -{{days}}, GETDATE())
  AND  hat_id = {{hat_id}}
ORDER  BY ts DESC, hat_id;

-- Time range — parameterized
SELECT ts,
       hat_id,
       uretim_adet
FROM   demo_fabrika.vardiya_uretim WITH (NOLOCK)
WHERE  ts >= {{start_date}}
  AND  ts <= {{end_date}}
ORDER  BY ts;

-- Shift summary
SELECT vardiya,
  SUM(uretim_adet)                AS total,
  AVG(CAST(uretim_adet AS FLOAT)) AS average,
  COUNT(*)                        AS row_count
FROM   demo_fabrika.vardiya_uretim WITH (NOLOCK)
WHERE  ts >= {{start_date}}
  AND  ts <= {{end_date}}
  AND  hat_id = {{hat_id}}
GROUP  BY vardiya;

-- DATEDIFF for day span
SELECT hat_id,
  DATEDIFF(day, MIN(ts), MAX(ts)) AS day_span
FROM   demo_fabrika.vardiya_uretim
WHERE  ts >= {{start_date}}
  AND  ts <= {{end_date}}
GROUP  BY hat_id;

T-SQL-specific notes: - Use TOP N — LIMIT N does not work in T-SQL - WITH (NOLOCK) avoids lock issues against live tables - Use GETDATE() for current time; DATEADD(unit, amount, date) for date arithmetic - Compute the difference between two dates with DATEDIFF(unit, start, end) - For names with spaces use the [table name] syntax

T-SQL editor — query written, contains TOP N / DATEADD

6. Write with Makinist (AI)

What is Makinist?

Makinist is the AI assistant built into the Compass SQL editor. Select your database tables as context, describe what you need in plain language, and Makinist returns a ready-to-run T-SQL query. Responses are generated via Langflow and session history is preserved.

In the Data Explore screen, click the Write with AI button in the top-right corner of the Write Query panel.

Data Explore — Write Query (left) + Table Preview (right); "Write with AI" button visible

The Makinist panel opens on the left; the right-side Table Preview remains active.

Makinist panel — empty session; session dropdown, Close Chat button, and Select Tables icon visible

UI Element Description
Makinist heading + 🤖 icon Indicates the left panel has switched to chat mode
Session dropdown Switch between past sessions; format: {DataSource}_{Month}_{HH:mm:ss}
🗑️ Delete icon Deletes the active session from Langflow; opens a new empty session
Close Chat Closes the Makinist panel; returns to Write Query mode
≡ (Select Tables) Opens the table selection modal — no message can be sent without a table
Input + ▶ Message composition and send area

1. Select Tables

Click the ≡ (Select Tables) icon at the bottom-left. The Select Tables modal opens listing all tables in your MSSQL database as checkboxes.

Select Tables modal — table selected

Check the table(s) you need and click OK. Selected tables appear as chips to the left of the input field; click the chip's × to remove individual tables.

Fewer tables, better results

Select only the minimum set of tables the query needs. No message can be sent without at least one table selected.

2. Type Your Request and Send

Type your request in English or Turkish and click ▶ (Send).

A typing... bubble appears while the response is being generated. Makinist's reply has two parts:

Makinist — user request + SQL block (Copy / Use SQL) + SQL Query Explanation

Part Description
User message Shown in a greenish bubble on the right
SQL code block Syntax-highlighted T-SQL code; Copy and Use SQL buttons at the top-right
SQL Query Explanation Text explaining what the query does

Specify T-SQL dialect

Include notes like "use TOP N, not LIMIT" or "calculate date ranges with DATEADD" so Makinist produces T-SQL-compliant output.

3. Transfer SQL to the Editor

Click the Use SQL button on the SQL code block.

"Use SQL" clicked — query transferred to editor; right panel switched to Query Result

  • The query is pasted into the Write Query editor with syntax highlighting.
  • The right panel automatically switches to Query Result mode and runs the query.
  • The Makinist panel closes; the Write with AI button reappears.

Convert hardcoded values to parameters

Use SQL transfers and runs the query as-is. If the generated SQL contains hardcoded values like 'HAT-1' or 7, replace them with {{hat_id}} / {{days}} format and set default values in the Parameters section.

7. Test the Query

Click the Run button. Results appear in tabular form.

T-SQL test result table

8. Save → Display Info

Click the Save button. The save panel opens.

Save panel

Field Required Default Example
Name Yes — mssql_shift_production
Description No — Last 7 days of shift production rows (MSSQL)

9. Global Variables

If {{parameter}} is used, define them in the left panel:

Field Required Description Default Example
Variable Name Yes The name inside {{...}} — start_date
Display Name Yes Dashboard label — Start Date
Type Yes Textbox / Datepicker / Dropdown Textbox Datepicker
Default Value No Initial value — 2024-01-01

10. Location — Target Folder

Pick the folder from the Target Folder field.

11. Click Save

Click the Save button. The query is saved to the Compass list.

Compass list — MSSQL query card

12. Common Errors

Error / Symptom Possible Cause Solution
mssql: login failed for user '...' Wrong username or password Update the MSSQL connection in Port
mssql: Cannot open database "..." requested by the login Wrong database name or no permission Check the connection settings in Port
mssql: TCP provider: connection refused MSSQL server unreachable Check Host, port, and network access
mssql: TLS Handshake failed SSL/TLS mismatch Check the SSL settings on the Port connection
'LIMIT' is not a recognized T-SQL keyword LIMIT is not valid in T-SQL Use SELECT TOP N
mssql: Invalid object name 'table' Table not found Verify the table name
mssql: Invalid object name 'demo_fabrika.vardiya_uretim' Demo Factory data not loaded See Synthetic Data Generation — MSSQL
mssql: Column 'col' does not exist Wrong column name Verify the column name in the table browser
"you already have a query named '...'" A query with the same name exists Enter a different name
Empty result The WHERE clause matches no data Add a NOLOCK hint; check filter values

Next Step

→ Cartography