ODBC Driver for QuickBooks Online

Connecting SQL Server Management Studio to QuickBooks Online via ODBC Driver



Requirements

In order to avoid incorrect integration with MS SSMS, the working environment must meet the following conditions:

Connecting to QuickBooks Online from SQL Server Management Studio using ODBC Driver for QuickBooks Online

You can use the Microsoft SQL Server Management Studio to connect your QuickBooks Online data to an SQL Server instance. Linked Server is a tool of MS SQL Server that allows to execute distributed queries to refer tables stored on non-SQL Server datbase in a single query. With linked servers, you can execute commands against different data sources such as QuickBooks Online and merge them with your SQL Server database. You can create a linked server with one of these methods: by using the options in the Object Explorer or by executing stored procedures.

Below are major advantages of using SQL Server Linked Servers to connect to QuickBooks Online:

  1. The ability to connect other database instances on the same or remote server.
  2. The ability to run distributed queries on heterogeneous data sources across the organization.
  3. The ability to work with diverse data sources in the same way.

How to configure a SQL Server Linked Server to connect to QuickBooks Online

You can follow the steps to create a linked server for QuickBooks Online in SQL Server Management Studio by using Object Explorer:

  1. Start your Management Studio and choose your SQL Server instance.
  2. In the Object Explorer pane, expand the Server Objects, right-click on Linked Servers and then click on New Linked Server.
  3. Configure your linked server in the dialog box:

The linked server will appear under the Linked Servers in the Object Explorer Pane. You can now issue distributed queries and access QuickBooks Online databases through SQL Server.

Retrieving Data From QuickBooks Online

Ensure the Allow inprocess option of MSDASQL OLE DB Provider for ODBC Drivers is enabled. For this, find the MSDASQL provider in the list of Linked Servers and double-click on it

msdasql

In the appeared Provider Options window, enable the Allow inprocess checkbox:

allowinprocess

Create a new Linked Server

newlinkedserver

Make sure to select Microsoft OLE DB Provider for ODBC Drivers:

provideroptionsepmty

Now you need to input the Linked Server name, e.g. QUICKBOOKS. In the Product Name and Data Source fields you need to indicate the System DSN that you've previously created - more info on System DSN setup can be found here.

provideroptions

The QuickBooks Online tables are already available to be fetched. To query the linked server, click New Query in the toolbar:

newquery

Enter your SQL query in the editor window and click Execute to run the query:

accounttable

As a result, you can see the contents of the selected table retrieved directly from the QuickBooks Online account you are connected to.

See also

© 2015-2024 Devart. All Rights Reserved. Request Support ODBC Forum Provide Feedback