With Coefficient's MS SQL integration, you gain effortless access to your MS SQL database server, enabling the smooth import of data to Google Sheets/Excel. You can easily transfer your data from your database by choosing tables and columns or creating your own queries. For those seeking expert assistance, GPT CoPilot is there to help you make custom queries for your Coefficient imports.
Schedule your Import, Snapshots, and Add Automations
Connecting to MS SQL
When you begin an MS SQL import for the first time, you will need to connect MS SQL as a data source for Coefficient.
ℹ️ NOTE: Coefficient will need the following information: Host, Database Name, Username, and Password when setting up your MS SQL connection. (The default MySQL port is 1433.)
|
1. Open the Coefficient Sidebar and click Menu. |
2. Select “Connected Sources”. |
3. Select “Add Connection” at the bottom and then “Connect” to MS SQL. |
|
4. Enter the required fields (Host, Database name, Username, Password, and Port). |
5. If your database is behind a firewall, you will need to whitelist (ALL 3) Coefficient's server IP addresses. (34.217.184.131, 44.234.233.60, 52.32.132.51). Click "Connect" when done. |
6. You will then be presented with the option to share this connection with other members of your team who also use Coefficient. Your credentials will NOT be shared with your team. 🎉 |
Advanced Settings
When setting up your Microsoft SQL connection, you can enable advanced settings to select a specific schema or configure SSH Tunnel encryption.
Visible Schemas
By default, all schemas are displayed when creating an import. If your database contains many schemas, you can improve load times by showing only a subset of them.
|
1. In the connection screen, click the "Advanced Settings" link and choose the schema you want to use for your data source connection. ℹ️ NOTE: To learn more about SQL Schema Limits, click here. |
SSH Tunnel
If your database server requires an SSH Tunnel for encryption, you can enable this option and enter your credentials in the connection screen.
|
1. In the connection screen, toggle the "Use SSH Tunnel" on and enter your SSH credentials:
|
Import from MS SQL
There are a few ways to import data using Coefficient from MS SQL, Importing from Tables & Columns, Importing from a Custom SQL Query, and Import from GPT SQL Builder.
Importing from Tables and columns allows you to create imports without having to write SQL. Using a Custom SQL Query gives you additional flexibility in the data that you are importing into Coefficient. And lastly, you can now prompt the Coefficient's AI to automatically build the SQL query for you. 🤯
Import from Tables & Columns
|
1. From the Sidebar select “Import from…”. |
2. Select "MS SQL Server" from the list. |
3. Select "From Tables & Columns". |
4. The Import Preview window opens, showing all the table schemas from your MS SQL database.
Select the table for your import or select a previously uploaded template. (eg. ”Employee”)5. Once the table is selected, the fields within that table will appear in a list on the left side of the Import Preview window. Select the fields you want to include in your import by checking/unchecking the corresponding boxes.
ℹ️ NOTE: The Import Preview shows only a sample of your data (50 rows). This sample data will be updated if there are any changes to the import's criteria.6. Customize your import by adding filters, sorts, limits, or even grouping the data into a cloud pivot table. You may also switch to the AI SQL Builder feature. Click "Import" when done.
7. Congratulations on your first MS SQL import using Tables & Columns! 🎉
Import from Custom SQL Query
|
1. From the Sidebar select “Import from…”. |
2. Select "MS SQL Server" from the list. |
3. Select "Custom SQL Query".
|
4. The Import Preview window opens, allowing you to enter your custom SQL query in the blue text box shown below, or use our AI to write your query. For further flexibility, you can use Coefficient’s SQL Parameters feature to point a value to a specific cell/range of cells for your query.
ℹ️ NOTE: Whenever you make changes to your query, you need to click "Refresh Preview" to update the sample data shown in the preview window.
5. When you click “Import” you will be prompted to give your import a name. The name MUST be UNIQUE, as it will also be the name of the tab in your Google Sheets/Excel when imported. (You can always change the name later if needed).6. Congratulations on your successful MS SQL Custom SQL import with Coefficient! 🎉
Import from GPT SQL Builder
|
1. From the Sidebar select “Import from…”. |
2. Select "MS SQL Server" from the list. |
3. Select "GPT SQL Builder". |
4. Choose your database schema and enter your prompt/query in the "Describe the query" box. (Example: "Show me a list of all employees and sort by hire date") When done, click "Generate SQL".
ℹ️ PRO TIP: Be specific when entering your prompts so that the AI can easily understand your requirements and provide more accurate results.
5. The SQL Builder will automatically generate and write the SQL query for you in the blue text box.
ℹ️ NOTE: Click "Refresh Preview" to display a sample of your data results (only 50 rows are shown) or to update the results of the preview if you make any changes to the query. 6. You will be prompted to give your import a name. Remember it MUST be UNIQUE as it will also be the name of the tab in your Google Sheets/Excel when imported. (You can always change the name later if needed).
7. Congratulations on your MS SQL import using Coefficient's GPT SQL Builder! 🎉
ℹ️ See GPT SQL Builder to learn more!
SQL Editor Features
The SQL Editor window (Custom SQL and GPT SQL Builder) includes features that enhance ease of use and familiarity, while adapting to native SQL tools.
|
Autocomplete Query |
Shows smart suggestions such as keywords, table names/fields as you type your query. |
|
Undo / Redo Query |
Return or revert query changes during the session session |
|
Inline Data Preview |
Preview changes from the import preview window as you modify the query |
|
Query Formatting |
Arrange queries in a more readable format with a simple right-click. |
|
Run Keys Shortcut |
Allows the use of Command + Enter to run queries |
|
In-place Parameter Referencing |
Parameters can be called from the query |
Export to MS SQL
In certain scenarios, you'll need to synchronize changes made in your spreadsheet with MS SQL records. Coefficient's exceptional capabilities extend to providing you with a direct, seamless pathway to push updates from your spreadsheet to MS SQL, offering unmatched efficiency and convenience. (Queue the celebration music!🎉 ).
ℹ️ See Export to SQL DB to learn more!
ℹ️ NOTE: Scheduled exports are supported for UPSERT and UPDATE actions. To learn more about Scheduled Exports, click here.
Schedule your Import, Snapshots, and Add Automations
Once you have pulled your data into your spreadsheet using Coefficient, you can set up the following:
|
|
FAQs for MS SQL Integration
I keep getting an error when I try to connect MS SQL to Coefficient. What is wrong?
There are a few things to try in this instance:
- Make sure that you have the correct MS SQL Hostname, Database Name, Username, Password, and Port for your MS SQL instance
- If your database is behind a firewall you will need to whitelist all (3) of our IP Addresses.
- 34.217.184.131
- 44.234.233.60
- 52.32.132.51
- Ensure that your MS SQL server and port are set to accept remote connections and are not just listening for incoming connections from localhost. Make sure that your server is accessible from the outside (internet) and not only hosted from your local PC/machine. This may require that you reach out to your MS SQL Server Admin to update some of your database settings.
When you connect Coefficient to MS SQL do you maintain access?
When Coefficient needs to run a query, we establish a connection to your database, run the query on your behalf, and terminate the connection once the query completes.
I added a table (or column) in my MS SQL database; why is it not showing up in Coefficient?
To deliver a snappy experience when you set up imports from MS SQL, we cache your database schema for up to 24 hours. If you recently changed your database schema (e.g., added a table/column, renamed a table/column, etc.), and you don't see the change reflected in Coefficient, you can force a schema reload:
- Open the Coefficient sidebar in Google Sheets/Excel.
- Click on the ≣ menu in the top right of the sidebar, then click on “Connected Sources”.
- Click on your MS SQL connection to see its Connection Settings page
- Click on the︙button near the top right, choose “Reload Schema”, and click “Reload” on the confirmation dialog.
My custom SQL script seems to run longer than expected and sometimes, I see a "SQL Error - canceling statement due to statement timeout" error when I refresh my import, what should I do?
The error message you're seeing indicates that the SQL query you're trying to execute is being canceled due to a statement timeout. This means that the query is taking too long to execute, and your database server is configured to cancel any query that exceeds a certain execution time threshold.
Here are some steps you can take to understand and fix this issue:
- Examine the Execution Plan: Use the EXPLAIN command to get the execution plan for your query. This will show you where the query might be inefficient, such as performing full table scans or using nested loops that could be optimized. (Click here to learn more about the EXPLAIN command with MS SQL).
- Optimize the Query: Look for ways to make the query more efficient. This could involve adding indexes to the columns used in the WHERE clause and the ILIKE conditions, rewriting the query to reduce complexity, or breaking it into smaller parts.
- Reduce the Dataset: If possible, limit the scope of the query. For example, if you're querying a large date range or a large number of rows, see if you can reduce that range with LIMIT.
- Increase the Statement Timeout (not recommended): If you have control over the database server settings, you can increase the statement timeout value. This is a temporary solution and may not be ideal if the query is inherently inefficient.