Sql Server Table Assistant

🚀 SQL Server Natural Language Query Tool Documentation

This project is based on Microsoft's Modal Context Protocol (MCP) and was initially implemented by Aman Pachori. It aims to simplify the operation of SQL Server databases through natural language processing technology. Users can ask questions in Chinese, and the tool will automatically generate and execute the corresponding SQL queries.

🚀 Quick Start

Prerequisites

• Operating System: Windows 10 or higher.
• .NET Framework: Version 4.7.2 or higher.
• ODBC Driver: Microsoft ODBC Driver for SQL Server must be installed.

Configuration Steps

1. Download and install the Microsoft ODBC Driver for SQL Server.
2. Create a configuration file config.json and fill in the following information:

   {
     "Server": "Your server address",
     "Database": "Your database name",
     "UID": "Your login name",
     "PWD": "Your password"
   }
   

3. Place the config.json file in the root directory of the tool.

✨ Features

Core Features

• Natural Language Query: Users can ask questions in Chinese, and the tool will automatically generate SQL queries.
• Result Interpretation: The tool will provide Chinese explanations of the results to help users understand the data.
• Query History: All executed queries are recorded for easy tracing and management.

Special Commands

• /diagnose: Perform a comprehensive table access diagnosis.
• /refresh_schema: Refresh the table structure information.
• /history: View all query records in the current session.

💻 Usage Examples

Basic Usage

> Show information about all employees with a salary exceeding 50,000.
SQL query generated: SELECT EmployeeName, Department, Salary FROM dbo.Employees WHERE Salary > 50000 ORDER BY Salary DESC;
Execution successful. The results are as follows:
+----------------+-------------+------+
| Employee Name  | Department  | Salary |
|----------------+------------+------|
| Zhang San      | Technology  | 60000 |
| Li Si          | Human Resources | 55000 |
| Wang Wu        | Marketing   | 70000 |
+----------------+-------------+------+
Result interpretation: The following are all employees with a salary exceeding 50,000, sorted in descending order of salary. Wang Wu has the highest salary, reaching 70,000.

> Show the maximum salary for each department.
SQL query generated: SELECT Department, MAX(Salary) AS MaxSalary FROM dbo.Employees GROUP BY Department;
Execution successful. The results are as follows:
+------------+-----------+
| Department | Maximum Salary |
|------------+-----------|
| Technology | 80000     |
| Human Resources | 65000 |
| Marketing  | 75000     |
+------------+-----------+
Result interpretation: The maximum salary in the Technology department is 80,000, obtained by Zhang San; the maximum salary in the Marketing department is 75,000, obtained by Wang Wu.

🔒 Security Precautions

1. Single-table Access: Queries are strictly limited to the configured tables.
2. Query Verification: All SQL queries will be shown to the user before execution to ensure the security of operations.
3. Transaction Security: Update, insert, and delete operations use transactions with rollback to ensure data integrity.
4. Error Logging: Detailed error information is recorded to facilitate problem troubleshooting without revealing sensitive information.
5. Prevent SQL Injection: The structured query generation mechanism reduces the risk of SQL injection.

📚 Documentation

Logging and Recording

All queries and their results are automatically saved in the logs/queries/ folder. Each log file contains:

• The original natural language query.
• All SQL iteration versions and feedback.
• The final executed SQL query.
• The query results.
• A timestamp.

Troubleshooting

Connection Issues

1. Confirm that the server address or IP is correct.
2. Check if the SQL Server is running and allows remote connections.
3. Review the firewall settings to ensure that the SQL Server port is open.
4. Verify that the ODBC driver is correctly installed and configured.

Memory Limitation Issues

1. Wait and Retry: Most... (The content seems incomplete here in the original text)