Read data from QuickBooks Online into SQL Server via ODBC Driver


QuickBooks Online LogoQuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using ODBC in few clicks. We will use ODBC JSON / REST API Driver to read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth Connection). We will discuss on How to Create an Intuit Developer Account, How to Create QuickBooks Online App for OAuth.

In nutshell, this post will focus on how to call QuickBooks Online API using ODBC.
So let’s get started.



Before we look into Step-By-Step section to extract and read data from QuickBooks Online to SQL Server let’s make sure you met the following requirements.

  1. A first requirement will be to SQL Server Database Engine Installed
  2. The second requirement will be SSMS installed
  3. Finally, make sure to have ZappySys ODBC PowerPack installed.

What is QuickBooks Online

QuickBooks is an accounting software package developed and marketed by Intuit. QuickBooks products are geared mainly toward small and medium-sized businesses and offer on-premises accounting applications as well as cloud-based versions that accept business payments, manage and pay bills, and payroll functions.

Getting Started

In order to start, we will show several examples. ZappySys includes an ODBC JSON / REST API Driver that will help you to call QuickBooks Online API, read various data from QuickBooks Online with ODBC. To learn more about QuickBooks Online API check this help file. To access QuickBooks Online data using REST API call, you need to create an Intuit Developer Account and then create QuickBooks Online App.

Create an Intuit Developer Account

Follow the mentioned steps below to create an Intuit developer account:
  1. you can visit this site, and click on Sign up from the menu bar.
  2. A registration form will be opened in a Popup. you can fill up and then click on Create Account.
    Signup Form: Intuit Developer Account

    Signup Form: Intuit Developer Account

  3. Complete the validation check and click Continue.
  4. On the confirmation dialog, provide a few details to fill out your profile and click All set.
Your account is created! You'll automatically be redirected to the app creation page to get coding on your app.
Note: Choose the user ID of your account carefully. Once an app is created in a given account it cannot be moved to another account or deleted. If you are building apps for a client, we recommend you create a dedicated account for that client that stays with them, leaving them full access.

Create a QuickBooks Online App

Follow the steps below to start creating your QuickBooks Online app.
  1. From the developer portal, click on My Apps.
  2. after that, Click Create a new app.
    Create new App: Select APIS

    Create a new App: Select APIS

  3. Click the Select APIs button under Just start coding.
  4. Select the APIs you wish to use. This selection can always be changed later via your app's settings.
  5. Your new app opens to its Dashboard tab.
  6. Click the Keys tab. This is where you can find all keys related to your app.
    Intuit App: Client Id,Secret Keys for OAuth Connection

    Intuit App: Client Id, Secret Keys for OAuth Connection

  7. To copy a key or token, click its Copy button.
Finally, we are ready with an App. Let´s start with an example. In this example, we will show how to retrieve all Invoices data from QuickBooks Online with ODBC.

Create ODBC – Native Zappysys JSON Driver Data Source

  1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:
    Open ZappySys Data Gateway

    Open ZappySys Data Gateway

  2. Add the Native – ZappySys JSON Driver Data source.
    Add Gateway Data Source (Native JSON Driver)

    Add Gateway Data Source (Native JSON Driver)

  3. Now it’s time to connect with QuickBooks. Let’s use JSON Driver Data source with the following URL:
    here, We have used 3 placeholders.
    * {RequestURL}
    is either a SandBox base URL or Production base URL.
    * {realmID} is your QuickBooks Online sandbox/production company ID.
    * {selectStatement} can be any query something like Select * From Invoice (*Note: select statement must be URL encoded first)
    For more information on Query, an Invoice Request API see this
    I am using my sandbox URL like this:

    Setup QuickBooks URL

  4. Configure the new OAuth Connection for QuickBooks Online as below.
    Basically, you need to configure the configurations of App on the General tab (i.e. OAuth Provider, Client ID, Client Secret, Scopes, etc.)

    SSIS OAuth Connection Manager: General Tab Configurations

    OAuth Connection Manager: General Tab Configurations

  5. Most importantly, you also need to Enter your Callback URL in Advanced Tab.
    SSIS OAuth Connection Manager: Advanced Tab Configurations

    OAuth Connection Manager: Advanced Tab Configurations

  6. Similarly, you need to check “Do not include credentials in header
    SSIS OAuth Connection Manager: OAuth2 Grant Options Tab Configurations

    OAuth Connection Manager: OAuth2 Grant Options Tab Configurations

  7. Click on Generate Token button to Generate token and then click on Test Connection button and click on OK to save the connection manager configurations settings.

    Generate Token and Test Connection

  8. Finally, now using Code Generator we will generate the query.

    Generate Query

Load QuickBooks Invoices data in MS SQL Server

Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.
  1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here: Get SQL Server Express and  Get SSMS
  2. Open SSMS and connect to SQL Server.
  3. Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server...
    Add Linked Server in SQL Server

    Add Linked Server in SQL Server

  4.  Now enter the linked server name, select Provider as SQL Native Client
  5. Enter data source as GatewayServerName, PORT_NUMBER where server name is where ZappySys Gateway is running (Can be same as SQL Server machine or remote machine). Default PORT_NUMBER is 5000 but confirm on Data gateway > General tab in case its different.
  6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column
    Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

    Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

  7. Click on Security Tab and select last option "Be made using this security context". Enter your gateway user account here.
  8. Click OK to save Linked Server
  9. In SSMS execute below SQL query to test your connectivity.
  10. Here is the preview after you run some REST API query in SQL Server. Notice that you can override default configuration by supplying many parameters in WITH clause (second query example in the screenshot).

    SSMS Output - Query REST API via Linked Server OPENQUERY statement (Connect to ZappySys Data Gateway)

  11. You can wrap your queries inside View or wrap inside Stored procedure to parameterize. Here is an example of creating the view which calls REST API queries.
    CREATE VIEW dbo.vw_MyAPICall_View 
    /*Call REST API inside SQL Server View*/
  12. Notice in above approach if you parameterize Stored Procedure then check this article to understand Dynamic Metadata.
  13. Now let's insert API data into the new data table "tblMyAPiData" in the SQL server database. For that, we need to execute below SQL query.

    Insert data into the new table

  14. Let's insert and update records into the already created table "tblMyAPiData"
    • To do that first we need to insert the new API data into the "#temp_tblMyAPiData" temporary database table.
    • Now let's delete the old records from the database table which are updated.
    • At the last insert all new API data into the database table.
    • To do that, we need to query like below SQL queries.
    DELETE FROM [dbo].[tblMyAPiData] WHERE id in (SELECT id FROM #temp_tblMyAPiData)
    INSERT INTO tblMyAPiData
    SELECT * FROM #temp_tblMyAPiData
  15. In the upper step, we see how to insert and update new records. Now if you want to update particular fields records only then you need to query like this.
    Update dbtbl_1
    set dbtbl_1.field1 = dbtbl_2.field1,
        dbtbl_1.field2 = dbtbl_2.field2
    FROM tblMyAPiData dbtbl_1
    *NOTE: If you are getting error like : "Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation" then you need to query like this :
    Update dbtbl_1
    set dbtbl_1.field1 = dbtbl_2.field1,
        dbtbl_1.field2 = dbtbl_2.field2
    FROM tblMyAPiData dbtbl_1
    COLLATE SQL_Latin1_General_CP1_CI_AS


So in this blog, we learned how to generate a QuickBooks API Token, Load QuickBooks Invoices data in MS SQL Server table using ODBC JSON / REST API Driver in a very simple way. You can achieve many more functionalities with this tool. Check our blogs/articles on JSON / REST API Driver to find out what this tool is capable of more.


Finally, you can use the following links for more information about the use of QuickBooks API with our tools:

Posted in JSON File / REST API Driver, REST API Integration, T-SQL (SQL Server) and tagged , , , , , .