[QODBC-Online] How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC Online/QRemote
Posted by Jack - QODBC Support on 19 August 2015 02:37 PM
How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC Online/QRemote
Start your "SQL Server Management Studio"
Login to your SQL Server.
On the Left-hand side panel, you will find the Server, Right Click on the "SQL Server" and Select Properties.
Please Verify that your SQL Server is 64-bit.
64 and 32-bit worlds are kept totally separate in the Microsoft Windows Operating System. QuickBooks is a 32-bit application, so in order to communicate with it, a 32-bit ODBC driver is required.
Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect, we call it QRemote.
QRemote has two components “QRemote Client Driver for QODBC Online” and “QRemote Server for QODBC Online”
QRemote Client Driver: Allows 64-bit Applications to connect to QRemote Server and sends the Request to QRemote Server for Processing.
QRemote Server: Processes requested instruction from QRemote Client to QuickBooks Online using QODBC DSN Driver.
Start “QRemote Server for QODBC Online” from the start menu. ( If QRemoteServer is not running )
Double click the QRemote Server icon from the system tray
QRemote Server Main screen shows the IP address and Port it allows the QRemote Client to connect to.
Make a note of IP address and Port number listed on the QRemote Server “Event Logs window”
Create a new DSN entry/or configure the existing one
Navigate to Start Menu, QODBC Drivers for Use with QuickBooks Online and click
Configure QODBC Online Data Source 64-bit
"QuickBooks Online Data 64-bit" is the default DSN created when installing QODBC.
Configure the DSN and provide the IP address and Port as listed under “QRemote Server”
Under the QRemote for QuickBooks Online– Client DSN configuration screen, Please fill in the following information
(Keep Encryption Key empty/blank. In case you want to use encrypted data transfer between QRemote client and QRemoteServer, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN)
Run MS SQL 2005 Dev / 2008 / 2012 (64-bit) and Login in
Run SQL Server Management Studio, you'd better choose the SQL Server Authentication and then input right name password.
Under SQL Server Management Studio , Navigate to “Server Objects” -> Linked Server -> New Linked Server
Provide the “New Linked Server” information
On Clicking OK, it will start establishing a connection to QuickBooks Online,The Linked server willbeshown under “Linked Server” Category.
Configure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers)
Dynamic parameter should be checked to allow parameter queries. Level zero only should be checked to allow four-part naming to work. Allow InProcess should be checked to help performance. Non transacted updates should be checked if you want to be able to update QuickBooks data. Index as access path should be checked to help performance. Disallow adhoc accesses should be unchecked to allow OPENQUERY to function.
QODBC Online/QRemote is ready to use with SQL Server/Linked Server
Execute below query
Select * from Online…Company
On each query, QRemote Server Logs the events, If you are facing any issues or delay in response, please refer QRemote Server Event Logs for details on which stage its taking more time.
Select * from Online…Account
On each query, QRemote Server Logs the events, If you are facing any issues or delay in response, please refer QRemote Server Event Logs for details on which stage it's taking more time.
Keywords: SQL Server 2008 Management Studio, SQL Server 2016 Management Studio, SQL Server 2012 Management Studio, SQL Server Management Studio, SSMS, QuickBooks Online, QBO, online 64-bit