# SJSINGLE_AI SQL Server MCP

Production-ready Model Context Protocol server for Microsoft SQL Server, configured for the `SJSINGLE_AI` database.

## Features

- SQL Server MCP tools for schema discovery and safe read-only queries
- `SJSINGLE_AI` configured as the default database
- Optional multi-database catalog through `databases.json`
- Read-only by default, with DDL/admin commands blocked
- Row limits and query timeout controls
- Live schema metadata discovery
- LangChain-powered natural-language read-only SQL tool
- stdio transport for Cursor and optional streamable HTTP transport

## Setup

```powershell
cd D:\DHRUVI_MCP_SERVER\DHRUVI_MCP_SERVER
copy .env.example .env
```

Edit `.env` and set the real SQL Server password:

```env
MSSQL_SERVER=4.240.84.65
MSSQL_PORT=1433
MSSQL_DATABASE=SJSINGLE_AI
MSSQL_DATABASES=SJSINGLE_AI
MSSQL_USER=sjreadonly
MSSQL_PASSWORD=your-secure-password
MSSQL_MAX_ROWS=10
AZURE_OPENAI_ENDPOINT=https://your-resource-name.openai.azure.com/
AZURE_OPENAI_DEPLOYMENT=your-foundry-model-deployment-name
AZURE_OPENAI_API_KEY=your-azure-openai-key
AZURE_OPENAI_API_VERSION=2024-10-21
```

Install dependencies:

```powershell
.\.venv\Scripts\python.exe -m pip install -e ".[dev]"
```

Run tests:

```powershell
.\.venv\Scripts\python.exe -m pytest
```

Start the MCP server:

```powershell
.\.venv\Scripts\python.exe -m sqlserver_mcp
```

For HTTP mode, keep these values in `.env`:

```env
MCP_TRANSPORT=streamable-http
MCP_HTTP_HOST=127.0.0.1
MCP_HTTP_PORT=8765
```

Then start the same command. The endpoint is:

```text
http://127.0.0.1:8765/mcp
```

## Cursor MCP

Use `mcp.json.example` as the Cursor MCP configuration template. It points at this folder and starts:

```powershell
.\.venv\Scripts\python.exe -m sqlserver_mcp
```

Keep `PYTHONPATH=D:\DHRUVI_MCP_SERVER\DHRUVI_MCP_SERVER\src` in the MCP environment so
Cursor loads the current source files, not an older installed package.

Runtime MCP instructions are passed inline from `src/sqlserver_mcp/server.py` through
FastMCP. `AGENT_INSTRUCTIONS.md` is kept as human-readable documentation and does
not need to be loaded into the MCP runtime.

Main tools:

- `list_databases`
- `list_schemas`
- `list_tables`
- `describe_table`
- `search_objects`
- `table_column_counts`
- `recommend_business_view`
<!-- - `get_party_detail` -->
- `get_stone_detail`
- `execute_query`
- `execute_parameterized_query`
- `answer_question_with_langchain`
- `get_database_info`

## Safety

The server is read-only unless `MSSQL_ALLOW_WRITE=true`. Even then, destructive/admin SQL such as `DROP`, `ALTER`, `CREATE`, `EXEC`, `TRUNCATE`, `BACKUP`, and `DBCC` remains blocked.

## LangChain

`answer_question_with_langchain` uses LangChain to generate one read-only T-SQL query from
a natural-language question. The generated query is still executed through the MCP server's
normal SQL validator and row cap.

Business questions are routed to preferred MCP views before SQL generation:

- Stone detail, stock, inventory, packet, or diamond questions: `MCP.VIEW_STOCK_STONE_DATA`
- Party eBid, bid, bidding, auction, or eBid result questions: `MCP.VIEW_EBID_RESULT_DETAILS_DATA`
- Party, party detail, customer profile, customer expression, interest, or preference questions: `MCP.VIEW_CUSTOMER_PROFILE_DATA` and `MCP.VIEW_CUSTOMER_EXPRESSION_DATA`
- Website activity, last activity, user tracking, page visit, or login activity questions: `MCP.VIEW_USER_TRACKING_LOG_DATA`

Preferred input columns:

- `MCP.VIEW_CUSTOMER_EXPRESSION_DATA`: `PARTY_COMPANY_NAME`
- `MCP.VIEW_CUSTOMER_PROFILE_DATA`: `WEB_USER_NAME` or `COMPANY_NAME`
- `MCP.VIEW_SALES_STONE_DATA`: `SERIAL_NO`
- `MCP.VIEW_STOCK_STONE_DATA`: `SerialNo` or `COMPANY_NAME`
- `MCP.VIEW_USER_TRACKING_LOG_DATA`: `COMPANY_NAME`
- `MCP.VIEW_EBID_RESULT_DETAILS_DATA`: `PARTY_COMPANY_NAME`

Query responses are capped at `10` rows.

For a direct stock stone serial lookup, use `get_stone_detail(serial_no="533007")`.
It performs one query against `[MCP].[VIEW_STOCK_STONE_DATA]` with a `SerialNo`
filter.

<!-- For party/customer detail by name, use `get_party_detail(party_name="QUALITY CHECK")`.
It checks both `[MCP].[VIEW_CUSTOMER_PROFILE_DATA]` and
`[MCP].[VIEW_CUSTOMER_EXPRESSION_DATA]` before returning, so a no-match response
means both views were checked. -->
Equivalent direct SQL:

```sql
SELECT TOP (1) * FROM [MCP].[VIEW_STOCK_STONE_DATA] WHERE [SerialNo] = SerialNo
```

For more than one serial, use `IN` and do not add `TOP (10)`:

```sql
SELECT * FROM [MCP].[VIEW_STOCK_STONE_DATA] WHERE [SerialNo] IN (SerialNo1, SerialNo2)
```

For highest stock cost, calculate cost as `GRATE * CARAT`:

```sql
SELECT TOP (1)
    [SERIAL_NO],
    [STONE_ID],
    [GRATE],
    [CARAT],
    ([GRATE] * [CARAT]) AS [CalculatedCost]
FROM [MCP].[VIEW_STOCK_STONE_DATA]
ORDER BY ([GRATE] * [CARAT]) DESC
```

For highest sale discount, run the direct sales view query once:

```sql
SELECT TOP 1 * FROM [MCP].[VIEW_SALES_STONE_DATA] ORDER BY [discount] DESC
```

Configure:

```env
# Azure AI Foundry / Azure OpenAI
AZURE_OPENAI_ENDPOINT=https://your-resource-name.openai.azure.com/
AZURE_OPENAI_DEPLOYMENT=your-foundry-model-deployment-name
AZURE_OPENAI_API_KEY=your-azure-openai-key
AZURE_OPENAI_API_VERSION=2024-10-21

LANGCHAIN_MODEL=gpt-4o-mini
LANGCHAIN_TEMPERATURE=0
LANGCHAIN_SCHEMA_TABLE_LIMIT=30
LANGCHAIN_INCLUDE_SQL=false
```

Schema discovery tools read live metadata from SQL Server on each call.