Connect SQL Server to HubSpot CRM

We're currently developing a HubSpot CRM ODBC driver, and so thought we'd share how you can use this connector to work with your HubSpot CRM data in SQL Server.

Work With HubSpot CRM Data by using a 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 Server Objects. From the pop-up menu choose New > Linked Server.
  3. In the Linked server box, type "HubSpot".
  4. From the Provider list, choose Microsoft OLE DB Provider for ODBC Drivers.
  5. In the Data source box, type the name of a HubSpot CRM 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 HubSpot CRM ODBC driver, it will not have the latest version of the System PATH environment variable. The HubSpot CRM 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 HubSpot CRM ODBC driver.
    • If you made a mistake when specifying the HubSpot CRM 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.
  6. In the Object Explorer pane, right-click Server Objects > Linked Servers > MSDASQL > Properties. Enable the Level zero only provider option. If you do not do this, you will get an error similar to The OLE DB provider "MSDASQL" for linked server "HUBSPOT" contains multiple tables that match the name "Companies" when querying your HubSpot data using T-SQL.
  7. You can query your HubSpot CRM data either by using a:
    • Four part table name in a distributed query. For example:

      SELECT * FROM HUBSPOT...Companies
      

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

      SELECT * FROM HUBSPOT...COMPANIES
      

      To check the capitalisation of the HubSpot CRM tables (objects), run:

      EXEC sp_tables_ex @table_server = 'HUBSPOT'
      
    • Pass-through query in an OPENQUERY function. For example:
      SELECT * FROM OPENQUERY(HUBSPOT, 'SELECT * FROM Companies')
      

      SQL Server sends pass-through queries as uninterpreted query strings to the HubSpot CRM 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.