run-sqlmcpserver-demos

# Run the SQL MCP Server demos Help the presenter execute the SQL MCP Server demos, one beat at a time, doing the right steps in the right order. The single source of truth for the steps is the runbook: **`sqlmcpserver/demo-runbook.md`** — read it first, then drive from it. The narrative/timing lives in `sqlmcpserver/outline.md`. All paths are workspace-relative to the **bobsql** root. Kit: `sqlmcpserver/build/`. Everything is local — no cloud egress. ## How to help 1. **Read `demo-runbook.md` first** every time — it has the current build state table (which beats are wired vs. TBD) and the exact commands/prompts. Do not invent steps that aren't there. 2. **Always run pre-flight before the first demo** (runbook "Pre-flight"): - **Standard pre-flight (restores Demo 2 config + goes live): `build/reset-dab-config.ps1`.** It restores `dab-config.json` from the committed baseline (the pristine **12-warnings** Demo 2 start state), validates it, then calls `start-mcp-http.ps1 -Restart` (full checks + start + MCP handshake). **Run this whenever the demo run includes Demo 2** — Demo 2's live fix edits the config, so restoring the baseline first is what lets it be run again identically. (Idempotent: if the config is already pristine, the restore is a no-op copy.) - **Lighter path (no config reset): `build/start-mcp-http.ps1`.** Same checks + start + handshake, but does **not** touch `dab-config.json`. Use when you're only doing the cold open / not re-running Demo 2. On **GO LIVE: GREEN**, VS Code connects via `.vscode/mcp.json` (`http://localhost:5001/mcp`). Stop with `-Stop`. - **Checks only** (or for the stdio alternate): `build/verify-preflight.ps1` — asserts DB reachable, 504 products, view returns 14 for Touring-1000, `dab --version` ≥ 2.0.9, `dab validate`, and that **≤ 1** DAB process is up. - **Only if using the stdio surface:** stop any OTHER SQL MCP server, then **Start** `Adventure Works (SQL MCP)` in *MCP: List Servers* (enabling ≠ starting), and warm up the first call (cold-start race — retry once). The HTTP path above avoids all of this. 3. **Drive one beat at a time.** State which demo you're on, show/run its steps, confirm the expected result, then ask before moving to the next. 4. **Verify with tools when useful.** You can call the AdventureWorks MCP tools directly (`describe_entities`, `read_records`) to prove a beat works before the presenter runs it in agent-mode chat. Load them with tool_search if deferred. 5. **If the agent misroutes** (returns tools/data you don't recognize): another MCP server is still connected — stop it and retry. This is the #1 gotcha. ## The beats (see the runbook for exact steps) - **Demo 1 — Cold open:** prompt *"What parts make up the Touring-1000 bike?"* → agent calls `describe_entities` then `read_records` (filter `ProductModel eq 'Touring-1000'`) → **14 components**, no SQL authored by the model. - **Demo 2 — One config, show/validate/query:** (1) show & discuss `dab-config.json` (per-entity surface toggles — REST off on the view, GraphQL/MCP default on), (2) basic DAB commands — `dab --version` / `--help` / `validate`; validate prints "Config is valid" **plus 12 "missing fields" MCP warnings**; on request, **add the fields live** (recipe below), (3) a GraphQL `productComponents` query in Nitro → 14. - **Demo 3 — Under the hood:** peel Demo 1 — server-description routing, `describe_entities` (no SQL), `read_records` → parameterized T-SQL. TWO panes: **Chat Debug view** (`/debug` → System prompt = server description, Tool responses = `describe_entities`/`read_records` payloads) + **XE via SSMS Watch Live Data**. Pre-flight the capture with `build/start-xe-capture.ps1` (creates+starts `dab_mcp_capture`) BEFORE the prompt; `describe_entities` = no row, `read_records` = one `rpc_completed` with bound `@param0`. Teardown `build/start-xe-capture.ps1 -Stop`. (Agent Logs preview was thin — not used.) ✅ verified 2026-07-26. - **Demo 4 — Add a tool live:** expose `dbo.uspGetProductBOM` as a `custom-tool`, restart, the agent discovers **`get_product_bom`** — the recursive BOM the view can't do (view = 14 top-level parts; tool = 87 rows, 4 levels). Deterministic payoff: `build/reset-dab-config.ps1 -ApplyTool` (applies `dab/dab-config.tool.json`, validates, restarts); reset with plain `reset-dab-config.ps1`. The proc INLINES the recursive CTE (a nested `EXEC` breaks `describe_first_result_set` and fails `dab validate`). ✅ built + verified 2026-07-26. - **Back pocket (not in the main run):** RBAC role-flip, RLS by `SalesPersonID`, don't-expose `EmployeePayHistory`, vector search (mention only). ## Demo 2 live fix — "add the fields dab validate is warning about" There is **NO single `dab` command** that auto-adds fields. Two ways to do the fix: **FAST PATH (recommended for the live talk) — pre-built, instant, deterministic:** When the presenter asks (after `dab validate` shows the "missing 'fields'" warnings), run: ```powershell ./sqlmcpserver/build/reset-dab-config.ps1 -ApplyFields ``` This drops in the pre-authored `dab-config.fields.json` (all **9 tables + 3 stored procs** described), validates to **0 warnings**, and restarts DAB — one move, ~seconds, identical every time. Then call `describe_entities` to show the model now sees the columns. Reset for the next run with `./reset-dab-config.ps1` (restores the 12-warnings baseline). **MANUAL PATH (only if you want to hand-author live to make a teaching point):** 1. Note the warned entities — the **9 tables** AND the **3 stored procs**. 2. For each warned TABLE, get its columns (schema/table = the entity's `source.object`): ```powershell sqlcmd -S localhost -E -C -W -d AdventureWorks -Q "SET NOCOUNT ON; SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Sales' AND TABLE_NAME='SalesOrderHeader' ORDER BY ORDINAL_POSITION;" ``` For each warned PROC, get its **result-set** columns (that is what a proc's `fields` describes — NOT its parameters): ```powershell sqlcmd -S localhost -E -C -W -d AdventureWorks -Q "SET NOCOUNT ON; SELECT name FROM sys.dm_exec_describe_first_result_set(N'EXEC dbo.uspGetManagerEmployees @BusinessEntityID=2', NULL, 0) ORDER BY column_ordinal;" ``` 3. Edit `dab-config.json`: add a `fields` array (sibling of `source`) to that entity — each column as `{ "name": "<col>", "description": "<short business description>" }`. Valid JSON; don't touch the other entities. - **Key fact (verified on DAB 2.0.9):** the `missing 'fields'` warning is keyed purely on the presence of a top-level `fields` array. Adding proc **`parameters`** does NOT clear it — only a **`fields`** block does. A `fields` block on a proc is additive metadata; it does not project/restrict the result set (the proc still returns all columns). 4. Re-run `dab validate` → confirm warnings gone ("Config is valid", **0** MCP warnings). 5. **ALWAYS restart** so the change takes effect the same way every time: `start-mcp-http.ps1 -Restart`, then call `describe_entities` to show the model now sees the columns. (DAB does **not** hot-reload config — without `-Restart` it serves stale config. This step is mandatory, not optional, so the beat is identical on every machine.) 6. **RESET after rehearsal:** `git checkout -- sqlmcpserver/build/dab/dab-config.json` (returns to the 12-warnings baseline so the warnings come back next run). Snappy is fine — the fast path adds all 12 entities in one move. Descriptions are short and useful (not one word); they're what the model reads via `describe_entities`. ## Key facts / gotchas - **DB:** local SQL Server 2025, `localhost`, **Windows integrated auth** (no container, no sa). AdventureWorks restored as `[AdventureWorks]`. - **MCP registration:** `.vscode/mcp.json` (workspace root) — `Adventure Works (SQL MCP)` is now **HTTP** on **:5001** (started by `start-mcp-http.ps1`). REST `/api`, GraphQL `/graphql`, and MCP `/mcp` all come from the one :5001 process. A **stdio** setup still works as an *alternate* (runbook collapsible section); if you use it, the flag is `--LogLevel` (capital). - **Opener is a VIEW + `read_records`** (built-in DML tool), deliberately — one question → one set-based query. It is NOT a custom-tool/stored-proc opener. - **After any config edit, use `start-mcp-http.ps1 -Restart`** — the plain command *reuses* a running DAB and serves **stale config** (verified: it kept serving the old server description until `-Restart`). This matters most in **Demo 4 (add-a-tool)**: without `-Restart` the new `get_product_bom` tool won't appear. - **DAB CLI stdio flag** is `--LogLevel` (capital), not `--loglevel`. - Do not start both MCP servers at once; the tools flyout can't tell them apart. ## Do NOT - Build/restore the database or edit `dab-config.json` schema here — that's the build kit's job (`download-`/`restore-adventureworks.ps1`, `build.ps1`, `sql/01-mcp-views.sql`).