Connect Microsoft SQL Server to Mammoth

What you can bring in from SQL Server

• Any table the login can read, grouped by schema (usually dbo).
• The result of your own T-SQL SELECT query.
• A query Mammoth writes from a plain-language description, when AI query is on for your workspace.

The dataset can refresh daily or only when you ask.

Before you connect SQL Server

You need:

• A SQL Server instance reachable from the internet.
• A SQL Server login with SELECT permission on the tables you want.
• The server's hostname or IP address, port (default 1433), database name, username and password.
• Mammoth's IP address allowed in Windows Firewall and any cloud security groups. See Allow Mammoth through your firewall.

Connect SQL Server to Mammoth

These steps follow the shared flow in Connect a database or app.

1. Go to the Data Library and click New dataset.
2. Choose Connectors.
3. Search for SQL Server and click its tile. The connection form opens.
4. Fill in the fields below. A callout below the fields shows the Mammoth IP address to allow in your database settings.
5. Click Connect. Mammoth checks the details and saves the connection. If the check fails, the error appears at the bottom of the form.

Once connected, the connector workspace opens on your tables.

Query a SQL Server table with T-SQL

1. In Tables, click a table. Mammoth opens its rows with the SELECT * query that read them, shown above the rows.
2. To change the query, click Edit SQL, edit it, and click Run (or press Ctrl+Enter).
3. To keep only some columns, tick them in the table header.

SQL Server uses square brackets around identifiers:

SELECT [OrderID], [CustomerID], [OrderDate], [TotalAmount]
FROM [dbo].[Orders]
WHERE [OrderDate] >= DATEADD(day, -90, GETDATE())
  AND [Status] = 'Completed'

Write a SQL Server query from a description

When AI query is on for your workspace, click Edit SQL to see the Describe the data you need... box above the editor.

1. Describe what you want, for example "all completed orders in the last 90 days".
2. Click Write SQL. Mammoth writes the query from your table structure.
3. Check the query, then click Run.

If a query fails, click Fix with AI to ask the chat to correct it. Connect a database or app covers the full SQL view.

Save SQL Server data as a dataset

1. Click Save as dataset.
2. Enter a Name and choose a folder under Save in.
3. Under Keep it up to date, choose Daily at 06:00 or Only when I ask.
4. Under On each refresh, click Change and pick what a refresh does (see the table below).
5. Click Save as dataset. When the dataset is ready, click Open dataset.

Refresh SQL Server data on a schedule

Choosing Daily at 06:00 creates a Dataset Refresh automation for the dataset. To run it on another schedule, edit that automation. For a dataset saved with Only when I ask, create a Dataset Refresh automation yourself.

After you save, you can change what a refresh does in the dataset's Update method setting. It offers Replace all data, Add new rows only and Keep only the newest rows. For large tables, see Speed up pipelines and connectors.

Troubleshoot SQL Server connections and logins

CREATE LOGIN mammoth_reader WITH PASSWORD = 'strong_password';
CREATE USER mammoth_reader FOR LOGIN mammoth_reader;
GRANT SELECT ON SCHEMA::dbo TO mammoth_reader;

Related

• Connect a database or app: the shared connector workspace steps and the full SQL view.
• Allow Mammoth through your firewall: the IP address to allow and how.
• Manage saved connections: edit, reuse or delete this connection.
• Refresh a dataset: change when the dataset refreshes.
• Speed up pipelines and connectors: strategies for large tables.