This documentation describes the integration of MindsDB with Microsoft SQL Server, a relational database management system developed by Microsoft. The integration allows for advanced SQL functionalities, extending Microsoft SQL Server’s capabilities with MindsDB’s features.

Prerequisites

Before proceeding, ensure the following prerequisites are met:

  1. Install MindsDB locally via Docker or use MindsDB Cloud.
  2. To connect Microsoft SQL Server to MindsDB, install the required dependencies following this instruction.

Connection

Establish a connection to your Microsoft SQL Server database from MindsDB by executing the following SQL command:

CREATE DATABASE mssql_datasource 
WITH ENGINE = 'mssql', 
PARAMETERS = {
    "host": "127.0.0.1",
    "port": 1433,
    "user": "sa",
    "password": "password",
    "database": "master"
};

Required connection parameters include the following:

  • user: The username for the Microsoft SQL Server.
  • password: The password for the Microsoft SQL Server.
  • host The hostname, IP address, or URL of the Microsoft SQL Server.
  • database The name of the Microsoft SQL Server database to connect to.

Optional connection parameters include the following:

  • port: The port number for connecting to the Microsoft SQL Server. Default is 1433.
  • server: The server name to connect to. Typically only used with named instances or Azure SQL Database.

Usage

Retrieve data from a specified table by providing the integration name, schema, and table name:

SELECT *
FROM mssql_datasource.schema_name.table_name
LIMIT 10;

Run T-SQL queries directly on the connected Microsoft SQL Server database:

SELECT * FROM mssql_datasource (

    --Native Query Goes Here
    SELECT 
      SUM(orderqty) total
    FROM Product p JOIN SalesOrderDetail sd ON p.productid = sd.productid
    JOIN SalesOrderHeader sh ON sd.salesorderid = sh.salesorderid
    JOIN Customer c ON sh.customerid = c.customerid
    WHERE (Name = 'Racing Socks, L') AND (companyname = 'Riding Cycles');

);

The above examples utilize mssql_datasource as the datasource name, which is defined in the CREATE DATABASE command.

Troubleshooting Guide

Database Connection Error

  • Symptoms: Failure to connect MindsDB with the Microsoft SQL Server database.
  • Checklist:
    1. Make sure the Microsoft SQL Server is active.
    2. Confirm that host, port, user, and password are correct. Try a direct Microsoft SQL Server connection using a client like SQL Server Management Studio or DBeaver.
    3. Ensure a stable network between MindsDB and Microsoft SQL Server.

SQL statement cannot be parsed by mindsdb_sql

  • Symptoms: SQL queries failing or not recognizing table names containing spaces or special characters.
  • Checklist:
    1. Ensure table names with spaces or special characters are enclosed in backticks.
    2. Examples:
      • Incorrect: SELECT * FROM integration.travel data
      • Incorrect: SELECT * FROM integration.‘travel data’
      • Correct: SELECT * FROM integration.`travel data`