The ODBC channel and MSSQL

As a developer, you can use the ODBC channel to connect to MSSQL databases and to Jira.

Before you connect

To setup the connection with MSSQL, we recommend that you

  • In your ODBC Driver (must be 64-bit) on your private network, ensure that you have created a “System User” Data Source Name (DSN). The DSN credentials will be used when you configure your first Pipeline using ODBC Channel steps.

  • Use TCP/IP Network Protocol in SQL Server

  • Ensure SQL browser service is turned on

How to Connect

  1. On the My pipelines page, click Create pipelines. Enter name, description, and tag fields and click the Create Pipelines button. The pipelines wizard displays:

  2. From the wizard you choose the connections for the first steps of your pipeline. When you choose a type, you'll add the channels and steps for your first steps. You can always add more steps later. A Triggered pipeline is started by a specific event in real-time. A Scheduled pipeline will start according to a schedule. A Manual pipeline only starts manually. Once you've completed the wizard, you'll be prompted for connection detail, if necessary. If you are an experienced builder, click the Start from scratch button and then from the right side of the page, choose the All to list all available channels.
  3. Expand ODBC and click Connect to ODBC

Click the Proxy connection check box to use the Pipelines Agent.

How to reconnect the ODBC channel

You may need to reconnect your account to a channel. Reasons may be (but not limited to):

  • If you need to connect a different account.

  • Authorization updates, such as a changed password.

  • Editing the access rights that Pipelines has to the channel.

To reconnect:

  1. Select a pipeline that already has ODBC in it.
  2. Open a step containing ODBC.
  3. Under account, select Connect (or reconnect) and follow the process above, How to connect.

Steps

The steps you can use with ODBC are: Queries and Rows.

Type Name Description
Queries
Action Execute SQL. Executes your SQL. This is intended for data modifications, such as INSERT, UPDATE, DELETE, etc. You should know the schema of the table, including table names, column names, data types, and param names. You also need whatever permissions are required for the action you want to perform. Your DB administrator will need to setup those permissions in the database — pipelines will not check for this.
Rows
Action Create a row. Select the table where you want to create a row. You can select up to 50 columns for values. Primary and Foreign Keys will be denoted, but are not required in the case of auto-generated values. There is no output for the Create step – if you want to fetch the value after creation, you will need to run a Search.
Query Search rows. Provides a SELECT query that will return a set of rows that can be iterated over. You need to know schema of the table, including table names, column names, data types. You can provide a list of up to 50 column names (comma-separated header), to map the results to specified data types. Provided column names must match the names in the results — alternatively, aliases can be used in the query, and the column names must match the aliases Options will appear with data type specifiers. If something is not available, you can use the default String, and transform the data later in the pipeline using Jinja expressions if needed. As a best practice, any dynamic filter criteria should be using query params, denoted by ? in the Query input. Provide an alias for each param (UP TO 50), corresponding to the location of the param in the query, for example:

SELECT * FROM companies WHERE company_id = ?
Options will appear with inputs where the user can provide values for the params

Limits

These are the known limits at this time:

  • Max 50 selected columns or params.
  • The "Create Row" step can only be used within the default schema (dbo).

 

Working with MSSQL

Here are some tips when working with MSSQL:

  • In some cases it is possible to use double quotes (“ “) but to do so, you need to check if QUOTED_INDENTIFIER setting is switched on.
  • To check the current state execute: SELECT sessionproperty('QUOTED_IDENTIFIER')

Escaping columns with special characters

Use square brackets ([ ]) to escape/select columns/identifiers which contain special characters in their name. For example:

SELECT [my column with spaces] as my_column FROM …

More about MSSQL Identifiers see: Database Identifiers

Query multiple databases using one query

You can query a multiple databases in one query in one of the following ways:

  1. Create a view that combines data from two tables in different databases and from pipelines query for that view like:

    Rows → Search Rows step

    SELECT * FROM context_db.dbo.view_that_collects_data_from_2_dbs

  2. Use a SQL query that combines data in different databases:

    SELECT somecolumn from DATABASE1.dbo.table1
    UNION
    SELECT othercolumn from DATABASE2.dbo.table2

  3. Query data from different databases:

    When you establish connection to odbc you are always in context of a database. You can specify a different database context from the DSN like

    DSN=mssql;UID=sa;PWD=YourSTRONG!Passw0rd; DATABASE=SOMEDATABASE;

    If you don’t specify a database by default you are in context of the master database. So if you want to query for a data that is in a different database you should specify the full table path which is usually like ex:

    SELECT * from SOMEDATABASE.dbo.SOMETABLE;

Example of usage

For examples of ODBC channel usage, see Quickbase Pipelines Agent use cases