Microsoft SQL Server

The Microsoft SQL Server query runner allows you to connect to and query Microsoft SQL Server databases, including Azure SQL Database.

Configuration

The SQL Server query runner requires the following configuration:

{
  "server": "127.0.0.1",
  "port": 1433,
  "user": "username",
  "password": "password",
  "db": "database_name",
  "tds_version": "7.0",
  "charset": "UTF-8"
}

Configuration Schema

Parameter	Type	Required	Description
server	String	Yes	SQL Server hostname or IP address (default: "127.0.0.1")
port	Number	No	SQL Server port (default: 1433)
user	String	No	Username for authentication
password	String	No	Password for authentication
db	String	Yes	Database name to connect to
tds_version	String	No	TDS protocol version (default: "7.0")
charset	String	No	Character set (default: "UTF-8")

Example Usage

from legion_query_runner.query_runner import QueryRunner

# Initialize with SQL Server configuration
mssql_config = {
    "server": "mssql.example.com",
    "port": 1433,
    "user": "username",
    "password": "password",
    "db": "mydatabase"
}

# Create a QueryRunner instance
runner = QueryRunner('mssql', mssql_config)

# Test connection
runner.test_connection()

# Execute a query
results = runner.run_query("SELECT TOP 10 * FROM users")

Type Mapping

The SQL Server query runner maps SQL Server data types to standardized types:

SQL Server Type ID	Legion Type
1	STRING
2	STRING
3	FLOAT
4	DATETIME
5	FLOAT

Query Limits

The SQL Server query runner has built-in support for limiting query results using TOP:

-- This will be automatically limited to 1000 rows
SELECT * FROM large_table

-- This will override the default limit and return 50 rows
SELECT TOP 50 * FROM large_table

Schema Retrieval

The SQL Server query runner extracts schema information from the INFORMATION_SCHEMA views:

# Get schema for all tables
schema = runner.get_schema()

# Get columns for a specific table
columns = runner.get_table_columns("users")

# Get column types for a specific table
column_types = runner.get_table_types("users")

Implementation Details

The SQL Server query runner uses the pymssql library to connect to SQL Server databases. This library must be installed for the query runner to be enabled.

To install the required dependency:

pip install pymssql

Troubleshooting

If you encounter connection issues, verify:

1. The server is accessible from your network
2. The port is open (default is 1433)
3. The user has proper permissions
4. TDS version is compatible with your SQL Server version