Sqldw Consumption Cli

> Update Check — ONCE PER SESSION (mandatory) > The first time this skill is used in a session, run the check-updates skill before proceeding. > - GitHub Copilot CLI / VS Code: invoke the check-updates skill. > - Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version. > - Skip if the check was already performed earlier in this session.

> CRITICAL NOTES > 1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering > 2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

SQL Endpoint Consumption — CLI Skill

Table of Contents

| Task | Reference | Notes | |---|---|---| | Finding Workspaces and Items in Fabric | [COMMON-CLI.md § Finding Workspaces and Items in Fabric](../../common/COMMON-CLI.md#finding-workspaces-and-items-in-fabric) | Mandatory — READ link first [needed for finding workspace id by its name or item id by its name, item type, and workspace id]| | Fabric Topology & Key Concepts | [COMMON-CORE.md § Fabric Topology & Key Concepts](../../common/COMMON-CORE.md#fabric-topology--key-concepts) || | Environment URLs | [COMMON-CORE.md § Environment URLs](../../common/COMMON-CORE.md#environment-urls) || | Authentication & Token Acquisition | [COMMON-CORE.md § Authentication & Token Acquisition](../../common/COMMON-CORE.md#authentication--token-acquisition) | Wrong audience = 401; read before any auth issue | | Core Control-Plane REST APIs | [COMMON-CORE.md § Core Control-Plane REST APIs](../../common/COMMON-CORE.md#core-control-plane-rest-apis) | Includes pagination, LRO polling, and rate-limiting patterns | | OneLake Data Access | [COMMON-CORE.md § OneLake Data Access](../../common/COMMON-CORE.md#onelake-data-access) | Requires storage.azure.com token, not Fabric token | | Job Execution | [COMMON-CORE.md § Job Execution](../../common/COMMON-CORE.md#job-execution) || | Capacity Management | [COMMON-CORE.md § Capacity Management](../../common/COMMON-CORE.md#capacity-management) || | Gotchas, Best Practices & Troubleshooting | [COMMON-CORE.md § Gotchas, Best Practices & Troubleshooting](../../common/COMMON-CORE.md#gotchas-best-practices--troubleshooting) || | Tool Selection Rationale | [COMMON-CLI.md § Tool Selection Rationale](../../common/COMMON-CLI.md#tool-selection-rationale) || | Authentication Recipes | [COMMON-CLI.md § Authentication Recipes](../../common/COMMON-CLI.md#authentication-recipes) | az login flows and token acquisition | | Fabric Control-Plane API via az rest | [COMMON-CLI.md § Fabric Control-Plane API via az rest](../../common/COMMON-CLI.md#fabric-control-plane-api-via-az-rest) | Always pass --resource; includes pagination and LRO helpers | | OneLake Data Access via curl | [COMMON-CLI.md § OneLake Data Access via curl](../../common/COMMON-CLI.md#onelake-data-access-via-curl) | Use curl not az rest (different token audience) | | SQL / TDS Data-Plane Access | [COMMON-CLI.md § SQL / TDS Data-Plane Access](../../common/COMMON-CLI.md#sql--tds-data-plane-access) | sqlcmd (Go) connect, query, CSV export | | Job Execution (CLI) | [COMMON-CLI.md § Job Execution](../../common/COMMON-CLI.md#job-execution) || | OneLake Shortcuts | [COMMON-CLI.md § OneLake Shortcuts](../../common/COMMON-CLI.md#onelake-shortcuts) || | Capacity Management (CLI) | [COMMON-CLI.md § Capacity Management](../../common/COMMON-CLI.md#capacity-management) || | Composite Recipes | [COMMON-CLI.md § Composite Recipes](../../common/COMMON-CLI.md#composite-recipes) || | Gotchas & Troubleshooting (CLI-Specific) | [COMMON-CLI.md § Gotchas & Troubleshooting (CLI-Specific)](../../common/COMMON-CLI.md#gotchas--troubleshooting-cli-specific) | az rest audience, shell escaping, token expiry | | Quick Reference | [COMMON-CLI.md § Quick Reference](../../common/COMMON-CLI.md#quick-reference) | az rest template + token audience/tool matrix | | Item-Type Capability Matrix | [SQLDW-CONSUMPTION-CORE.md § Item-Type Capability Matrix](../../common/SQLDW-CONSUMPTION-CORE.md#item-type-capability-matrix) | Read first — shows what's read-only (SQLEP) vs read-write (DW) | | Connection Fundamentals | [SQLDW-CONSUMPTION-CORE.md § Connection Fundamentals](../../common/SQLDW-CONSUMPTION-CORE.md#connection-fundamentals) | TDS, port 1433, Entra-only, no MARS | | Supported T-SQL Surface Area (Consumption Focus) | [SQLDW-CONSUMPTION-CORE.md § Supported T-SQL Surface Area](../../common/SQLDW-CONSUMPTION-CORE.md#supported-t-sql-surface-area-consumption-focus) | Read before writing T-SQL — includes data types (no nvarchar/datetime/money) | | Read-Side Objects You Can Create | [SQLDW-CONSUMPTION-CORE.md § Read-Side Objects You Can Create](../../common/SQLDW-CONSUMPTION-CORE.md#read-side-objects-you-can-create) | Views, TVFs, scalar UDFs, procedures | | Temporary Tables | [SQLDW-CONSUMPTION-CORE.md § Temporary Tables](../../common/SQLDW-CONSUMPTION-CORE.md#temporary-tables) | Use DISTRIBUTION = ROUND_ROBIN for INSERT INTO SELECT support | | Cross-Database Queries | [SQLDW-CONSUMPTION-CORE.md § Cross-Database Queries](../../common/SQLDW-CONSUMPTION-CORE.md#cross-database-queries) | 3-part naming, same workspace | | Security for Consumption | [SQLDW-CONSUMPTION-CORE.md § Security for Consumption](../../common/SQLDW-CONSUMPTION-CORE.md#security-for-consumption) | GRANT/DENY, RLS, CLS, DDM | | Monitoring and Diagnostics | [SQLDW-CONSUMPTION-CORE.md § Monitoring and Diagnostics](../../common/SQLDW-CONSUMPTION-CORE.md#monitoring-and-diagnostics) | Includes query labels; DMVs (live) + queryinsights.* (30-day history) | | Performance: Best Practices and Troubleshooting | [SQLDW-CONSUMPTION-CORE.md § Performance: Best Practices and Troubleshooting](../../common/SQLDW-CONSUMPTION-CORE.md#performance-best-practices-and-troubleshooting) | Statistics, caching, clustering, query tips | | REST API: Refresh SQL Endpoint Metadata | [SQLDW-CONSUMPTION-CORE.md § REST API: Refresh SQL Endpoint Metadata](../../common/SQLDW-CONSUMPTION-CORE.md#rest-api-refresh-sql-endpoint-metadata) | Force metadata sync when SQLEP data is stale after ETL | | System Catalog Queries (Metadata Exploration) | [SQLDW-CONSUMPTION-CORE.md § System Catalog Queries](../../common/SQLDW-CONSUMPTION-CORE.md#system-catalog-queries-metadata-exploration) | sys.tables, sys.columns, sys.views, sys.stats | | Common Consumption Patterns (End-to-End Examples) | [SQLDW-CONSUMPTION-CORE.md § Common Consumption Patterns](../../common/SQLDW-CONSUMPTION-CORE.md#common-consumption-patterns-end-to-end-examples) | Reporting views, cross-DB analytics, temp table staging | | Gotchas and Troubleshooting Reference | [SQLDW-CONSUMPTION-CORE.md § Gotchas and Troubleshooting Reference](../../common/SQLDW-CONSUMPTION-CORE.md#gotchas-and-troubleshooting-reference) | 18 numbered issues with cause + resolution | | Quick Reference: Consumption Capabilities by Scenario | [SQLDW-CONSUMPTION-CORE.md § Quick Reference: Consumption Capabilities](../../common/SQLDW-CONSUMPTION-CORE.md#quick-reference-consumption-capabilities-by-scenario) | Scenario → approach lookup | | Schema and Object Discovery | [discovery-queries.md § Schema and Object Discovery](references/discovery-queries.md#schema-and-object-discovery) | Tables, columns, views, functions, procedures, cross-DB | | Security Discovery | [discovery-queries.md § Security Discovery](references/discovery-queries.md#security-discovery) || | Statistics and Performance Metadata | [discovery-queries.md § Statistics and Performance Metadata](references/discovery-queries.md#statistics-and-performance-metadata) || | Bash — Data Export | [script-templates.md § Bash — Data Export](references/script-templates.md#bash--data-export) | Query to CSV + parameterized date range export | | Bash — Schema Discovery Report | [script-templates.md § Bash — Schema Discovery Report](references/script-templates.md#bash--schema-discovery-report) || | Bash — Performance Investigation | [script-templates.md § Bash — Performance Investigation](references/script-templates.md#bash--performance-investigation) || | PowerShell Templates | [script-templates.md § PowerShell Templates](references/script-templates.md#powershell-templates) | Query to CSV + schema discovery | | Tool Stack | [SKILL.md § Tool Stack](#tool-stack) || | Connection | [SKILL.md § Connection](#connection) || | Agentic Exploration ("Chat With My Data") | [SKILL.md § Agentic Exploration](#agentic-exploration-chat-with-my-data) | Start here for data exploration | | Script Generation | [consumption-cli-quickref.md § Script Generation](references/consumption-cli-quickref.md#script-generation) | Formatting flags, piped input, parameterized queries | | Monitoring and Performance | [consumption-cli-quickref.md § Monitoring and Performance](references/consumption-cli-quickref.md#monitoring-and-performance) | Active queries DMV, KILL syntax | | Gotchas, Rules, Troubleshooting | [SKILL.md § Gotchas, Rules, Troubleshooting](#gotchas-rules-troubleshooting) | MUST DO / AVOID / PREFER checklists | | Agent Integration Notes | [consumption-cli-quickref.md § Agent Integration Notes](references/consumption-cli-quickref.md#agent-integration-notes) | Per-agent CLI tips |

---

Tool Stack

| Tool | Role | Install | |---|---|---| | sqlcmd (Go) | Primary: Execute T-SQL. Standalone binary, no ODBC driver, built-in Entra ID auth via DefaultAzureCredential. | winget install sqlcmd / brew install sqlcmd / apt-get install sqlcmd | | az CLI | Auth (az login), token acquisition, Fabric REST for endpoint discovery. | Pre-installed in most dev environments | | jq | Parse JSON from az rest | Pre-installed or trivial |

> Agent check — verify before first SQL operation: > ``bash > sqlcmd --version 2>/dev/null || echo "INSTALL: winget install sqlcmd OR brew install sqlcmd" > ``

---

Connection

Discover the SQL Endpoint FQDN

Per [COMMON-CLI.md](../../common/COMMON-CLI.md) Discovering Connection Parameters via REST:

WS_ID=""
ITEM_ID=""

# Warehouse
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/warehouses/$ITEM_ID" \
  --query "properties.connectionString" --output tsv

# Lakehouse SQL endpoint
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/lakehouses/$ITEM_ID" \
  --query "properties.sqlEndpointProperties.connectionString" --output tsv

Result: .datawarehouse.fabric.microsoft.com

Connect with sqlcmd (Go)

# Interactive session (Entra login via browser if needed)
sqlcmd -S ".datawarehouse.fabric.microsoft.com" -d "" -G

# Non-interactive one-shot query
sqlcmd -S ".datawarehouse.fabric.microsoft.com" -d "" -G \
  -Q "SELECT TOP 10 * FROM dbo.FactSales"

# Explicit ActiveDirectoryDefault (uses az login session)
sqlcmd -S ".datawarehouse.fabric.microsoft.com" -d "" \
  --authentication-method ActiveDirectoryDefault \
  -Q "SELECT TOP 10 * FROM dbo.FactSales"

# Service principal (CI/CD)
SQLCMDPASSWORD="" \
sqlcmd -S ".datawarehouse.fabric.microsoft.com" -d "" \
  --authentication-method ActiveDirectoryServicePrincipal \
  -U "" \
  -Q "SELECT COUNT(*) FROM dbo.FactSales"

Reusable Connection Variables

# Set once at script top
FABRIC_SERVER=".datawarehouse.fabric.microsoft.com"
FABRIC_DB=""
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

# Use throughout
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.DimProduct"
$SQLCMD -i myscript.sql

PowerShell / Windows CMD

# PowerShell
$s = ".datawarehouse.fabric.microsoft.com"; $db = ""
sqlcmd -S $s -d $db -G -Q "SELECT TOP 10 * FROM dbo.FactSales"
# CMD: use set S=... and %S% / %DB% instead of $variables

---

Agentic Exploration ("Chat With My Data")

Schema Discovery Sequence

Run these in order to understand what's in the endpoint. See [references/discovery-queries.md](references/discovery-queries.md) for extended discovery queries.

# 1. List schemas
$SQLCMD -Q "SELECT schema_name FROM information_schema.schemata ORDER BY schema_name" -W

# 2. List tables and views
$SQLCMD -Q "SELECT table_schema, table_name, table_type FROM information_schema.tables ORDER BY table_schema, table_name" -W

# 3. Columns for a table
$SQLCMD -Q "SELECT column_name, data_type, character_maximum_length, is_nullable FROM information_schema.columns WHERE table_schema='dbo' AND table_name='FactSales' ORDER BY ordinal_position" -W

# 4. Preview rows
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.FactSales" -W

# 5. Row counts
$SQLCMD -Q "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS row_count FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY row_count DESC" -W

# 6. Programmability objects (views, functions, procedures)
$SQLCMD -Q "SELECT name, type_desc FROM sys.objects WHERE type IN ('V','FN','IF','P','TF') ORDER BY type_desc, name" -W

Agentic Workflow

1. Discover → Run Steps 1–3 to understand available tables/columns.
2. Sample → SELECT TOP 5 on relevant tables.
3. Formulate → Write T-SQL using [SQLDW-CONSUMPTION-CORE.md](../../common/SQLDW-CONSUMPTION-CORE.md) Supported T-SQL Surface Area.
4. Execute → $SQLCMD -Q "...".
5. Iterate → Refine based on results.
6. Present → Show results or generate a reusable script (Script Generation section).

---

Gotchas, Rules, Troubleshooting

For full T-SQL/platform gotchas: [SQLDW-CONSUMPTION-CORE.md](../../common/SQLDW-CONSUMPTION-CORE.md) Gotchas and Troubleshooting Reference and [COMMON-CLI.md](../../common/COMMON-CLI.md) Gotchas & Troubleshooting (CLI-Specific).

MUST DO

• Always -d — FQDN alone is insufficient.
• Always -G or --authentication-method — SQL auth not supported on Fabric.
• az login first — ActiveDirectoryDefault uses az session. No session → cryptic failure.
• SET NOCOUNT ON; in scripts — suppresses row-count messages that corrupt output.
• Label queries with OPTION (LABEL = 'AGENTCLI_...') for Query Insights tracing.

AVOID

• ODBC sqlcmd (/opt/mssql-tools/bin/sqlcmd) — requires ODBC driver. Use Go version.
• Omitting -W in scripts — trailing spaces corrupt CSV.
• DML on SQLEP — Lakehouse/Mirrored DB endpoints are read-only. DML only on Warehouse.
• MARS — not supported. Remove MultipleActiveResultSets from connection strings.
• Hardcoded FQDNs — discover via REST API (Discover the SQL Endpoint FQDN).

PREFER

• sqlcmd (Go) -G over curl+token for SQL queries.
• -Q (non-interactive exit) for agentic use.
• Piped input for multi-statement batches or queries with quotes.
• -i file.sql for complex queries — avoids shell escaping.
• -F vertical for exploration of wide tables.
• Env vars (FABRIC_SERVER, FABRIC_DB) for script reuse.
• az rest for Fabric REST API — use sqlcmd only for T-SQL.

TROUBLESHOOTING

| Symptom | Cause | Fix | |---|---|---| | Login failed for user '' | Wrong DB name or no access | Verify -d matches item name exactly (case-sensitive) | | Cannot open server | Wrong FQDN or network | Re-discover via REST API; check port 1433 | | Login timeout expired | Port 1433 blocked | nc -zv 1433; check firewall/VPN | | ActiveDirectoryDefault failure | az login expired or wrong tenant | az login --tenant | | Garbled CSV output | Missing -W or wrong -s | Add -W -s"," -w 4000 | | (N rows affected) in file | No SET NOCOUNT ON | Prepend SET NOCOUNT ON; | | Invalid object name 'queryinsights...' | New warehouse < 2 min old | Wait ~2 minutes | | No rows but data exists | RLS filtering | Check `USE

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: microsoft
• Source: microsoft/skills-for-fabric
• License: MIT

Install and usage instructions live in the source repository linked above.