Skip to main content

​
Intro

Microsoft SQL Server is a repository for your business information, but you need to provide easy access to critical metrics. Domo connects directly to SQL Server and delivers the information you need in real-time visualizations that make analysis easier. Moreover, you can see SQL Server data alongside metrics from any other system, all in a single platform, and get instant notifications when your SQL Server metrics hit thresholds that you determine. Using the Domo Microsoft SQL Server connector, you can query data from your SQL Server using standard SQL queries. Once your data is in Domo, you can collaborate with your team where your data lives—so nothing gets lost in translation. Stop waiting days or weeks for reports. With real-time MSSQL data, you can have confidence in the metrics you share with stakeholders. Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). You can connect to your Microsoft SQL Server in the Data Center. This article explains the fields and menus specific to the Microsoft SQL Server connector user interface. To add DataSets, set update schedules, and edit DataSet information, see Adding a DataSet Using a Data Connector.

​
Prerequisites

To connect to a Microsoft SQL Server database and create a DataSet, you must have the following:
  • Username
  • Password
  • Host
  • Port
  • Database
  • Certificate (optional)
  • Additional JDBC Properties (optional)
Before you can connect to your SQL server, you must also whitelist a number of IP addresses on your database server on the port you want to connect to. For the full list of IP addresses, see Whitelisting IP Addresses for Connectors.

​
Connect to Your Microsoft SQL Server Database

This section enumerates the options in the Credentials and Details panes in the Microsoft SQL Server Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in greater length in Adding a DataSet Using a Data Connector.

​
Credentials Pane

This pane contains fields for entering credentials to connect to your database. The following table describes what is needed for each field:

Field

Description

Username

MS SQL Username

Password

Database password

Host

Database hostname

Port

Database port number

Database

SSL CA Certificate download url

Certificate

Enter the comma separated additional JDBC properties here.
Example: property1=value1,property2=value2

Once you have entered valid Microsoft SQL Server credentials, you can use the same account any time to create a new Microsoft SQL DataSet. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Managing User Accounts for Connectors.

​
Details Pane

In this pane you create an SQL query to pull data from your database.
Menu Description
Query Type

Select a query type.

QueryType Description
Custom Query

Enter the SQL query to execute. When you select the query types as Custom Query , the following fields populate:

  • Query
  • Query Parameter (checkbox)
  • Fetch Size
  • Query Parameter
Query Builder

Select a table and fields to autogenerate your query. When you select the query types as Query Builder , the following fields populate:

  • Fetch Size
  • Database Tables
  • Table Columns
  • Query
Fetch Size (optional) Enter the fetch size, which is the number of rows requested for each page of data. 10,000 rows is the recommended maximum fetch size. If your dataset throws an out of memory error, try decreasing the fetch size.
Database Tables Select the table.
Table Columns Select the columns.
Query When you select any table column, the corresponding query appears. in this field. Note: This field appears when you select the Query Type as Query Builder .
Query Enter the SQL query to execute. The query gets executed on the MySQL server and fetches the data. Note: This field appears when you select the Query Type as Custom Query .
Query Parameter (checkbox) Select this checkbox to add query parameters.
Query Parameter Enter the query parameter value, which is the initial value for query parameter. The last run date is optional by default and the date is ‘02/01/1700’ if is not provided. For example: !{lastvalue:_id}!=1,!{lastrundate:start_date}!=02/01/1944

​
Other Panes

For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Data Connector.