Connect to QuickBooks Online from SQL Server

The QuickBooks Online ODBC driver enables you to work with QuickBooks Online data in SQL Server just as if you were working with data stored in a SQL Server table.

Download the QuickBooks Online ODBC driver. Then follow the instructions in this article to install and license the driver and set up the ODBC data source that enables you to connect SQL Server to QuickBooks online.

Linked server

1. In Microsoft SQL Server Management Studio, connect to the SQL Server instance you want to create the linked server against.

   You need to log on with an account that is a member of the SQL Server sysadmin fixed server role to create a linked server.

2. Right-click instance > Server Objects > MSDASQL > Properties.

   The Microsoft OLE DB Provider for ODBC Drivers Provider Options dialog box is displayed.

3. Enable the Level zero only option.

   If you don't do this, you will get a "linked server contains multiple tables that match the table table name." error when querying your QuickBooks Online data with a 4 part T-SQL query.

4. Right-click Server Objects. From the pop-up menu choose New > Linked Server.
5. In the Linked Server box, enter QBONLINE.
6. From the Provider list, choose Microsoft OLE DB Provider for ODBC Drivers.
7. In the Data source box, enter the name of your QuickBooks Online ODBC data source, and then choose OK.

   SQL Server verifies the linked server by testing the connection.

   • If you get the error "Specified driver could not be loaded due to system error 126: The specified module could not be found," choose Yes when prompted whether to keep the linked server. You need to restart your SQL Server instance before you can use the linked server. If SQL Server was already running when you installed the QuickBooks Online ODBC driver, it will not have the latest version of the System PATH environment variable. The QuickBooks Online ODBC driver Setup program adds entries for the driver to the System PATH. Restarting the instance makes these changes available to SQL Server, allowing it to load the QuickBooks Online ODBC driver.
   • If you made a mistake when specifying the QuickBooks Online ODBC data source, you get the error "Data source name not found and no default driver specified." If you get this error, choose No when prompted whether to keep the linked server and edit the value in the Data source box.
8. You can query your QuickBooks Online data either by using a:
   • Four-part table name in a distributed query.

     A four-part table name has the format:

     server_name.[database_name].[schema_name].table_name

     QuickBooks Online does not have a database name or schema, so omit these from your SQL statement. For example:

     SELECT * FROM QBONLINE...Account

     The capitalisation of the table name must be the same as it is in QuickBooks Online. For example, the following query is invalid:

     SELECT * FROM QBONLINE...ACCOUNT

     To check the capitalisation of the QuickBooks Online tables (objects), run:

     EXEC sp_tables_ex @table_server = 'QBONLINE'

   • Pass-through query in an OPENQUERY function. For example:

     SELECT * FROM OPENQUERY (QBONLINE, 'SELECT * FROM ACCOUNT')

     SQL Server sends pass-through queries as uninterpreted query strings to the QuickBooks Online ODBC driver. This means that SQL Server does not apply any kind of logic to the query or try to estimate what that query will do.

OPENDATASOURCE and OPENROWSET

The OPENDATASOURCE or OPENROWSET functions enable you to work with QuickBooks Online data without configuring a linked server. There are some security implications associated with their use and they are not therefore enabled by default.

1. In Microsoft SQL Server Management Studio, run these commands to enable the OPENDATASOURCE and OPENROWSET functions:

   EXEC sp_configure 'show advanced options', 1
   RECONFIGURE
   GO
   EXEC sp_configure 'ad hoc distributed queries', 1
   RECONFIGURE
   GO

2. Run:

   SELECT * FROM OPENDATASOURCE('MSDASQL', 'DSN=MY_QUICKBOOKSONLINE_ODBC_DATA_SOURCE;') .SF.DBO.Account

   –Or–

   SELECT * FROM OPENROWSET('MSDASQL', 'DSN=MY_QUICKBOOKSONLINE_ODBC_DATA_SOURCE;', 'select * from Account;')

Example: Backing up a QuickBooks Online table in SQL Server

This command creates a copy of the Account table. It assumes the linked server is named QBONLINE.

IF OBJECT_ID('dbo.Account', 'U') IS NOT NULL DROP TABLE dbo.Account;
SELECT * INTO Account FROM OPENQUERY(QBONLINE,'SELECT * FROM Account')