# MCP SQL Server

A [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server that exposes a SQL Server database as tools for AI assistants like Claude, Gemini, and Copilot.

## Features

- **`test_connection`** — Verify database connectivity
- **`list_tables`** — List all tables in the database
- **`query_sql`** — Execute arbitrary SQL queries (read/write with 100-row limit)
- **`get_oee_by_line`** — Calculate OEE metrics (Availability, Performance, Quality) for a production line
- **`get_downtime_analysis`** — Aggregate downtime reasons sorted by duration
- **`get_shift_performance`** — KPI analytics for a specific shift and date
- **`get_live_kpi`** — Fetch the latest OEE value from the live table

## Prerequisites

- Python 3.10+
- Microsoft ODBC Driver 17+ for SQL Server
- Access to a SQL Server database

## Installation

```bash
# Clone the repository
git clone https://github.com/gigimento/mcp-sql-server.git
cd mcp-sql-server

# Create virtual environment
python -m venv .venv
.venv\Scripts\activate  # Windows
# source .venv/bin/activate  # Linux/macOS

# Install dependencies
pip install -r requirements.txt
```

## Configuration

Copy `.env.example` to `.env` and adjust:

```env
DB_SERVER=localhost\SQLEXPRESS
DB_NAME=YourDatabase
DB_DRIVER=ODBC Driver 17 for SQL Server
DB_TRUSTED_CONNECTION=yes
```

Or set the corresponding environment variables directly.

## Usage

### With Claude Desktop / OpenCode

Add to your MCP config:

```json
{
  "mcpServers": {
    "sql-server": {
      "command": "python",
      "args": ["path/to/server.py"],
      "env": {
        "DB_SERVER": "localhost\\SQLEXPRESS",
        "DB_NAME": "YourDatabase"
      }
    }
  }
}
```

### Standalone

```bash
python server.py
```

The server communicates over stdio using the MCP protocol.

## Expected Database Schema

The OEE tools expect tables similar to:

- `ProductionData` — columns: `Line`, `Date`, `Shift`, `Runtime`, `PlannedTime`, `GoodQty`, `RejectQty`, `TotalQty`, `PlannedQty`
- `Downtime` — columns: `Line`, `Date`, `DowntimeReason`, `Duration`
- `OEE_Live` — columns: `Line`, `OEE`, `Timestamp`

You can adapt the queries to match your schema by editing the tool functions in `server.py`.

## License

MIT