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 Docker Desktop.
  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`
Microsoft AccessMonetDB