[QODBC-Desktop] How to create Link Server with MS SQL 2005/2008/2012/2016 64-bit Using QODBC/QRemote
Posted by Rajendra Dewani (QODBC Support) on 04 October 2012 12:39 PM

How to create Link Server with MS SQL 2005/2008/2012/2016 64-bit Using QODBC/QRemote

 For 32-bit Refer: How to create Link Server with MS SQL 2005/2008/2012/2016 32-bit Using QODBC/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 words are kept separate in the Microsoft Windows Operating System. QuickBooks 2021 and older versions are 32-bit applications, so a 32-bit ODBC driver is required to communicate with it.

QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-bit Web Servers and 64-bit applications to talk to QuickBooks live for the first time via the QODBC Driver.

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" and "QRemote Server for QODBC." 

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 using QODBC DSN Driver.  

Start "QRemote Server for QODBC" 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 allowing the QRemote Client to connect.

Note the 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, and click

Configure QODBC Data Source 64-bit  

"QuickBooks Data 64-bit QRemote" is the default DSN created when installing QODBC. (Earlier, it was "QRemote Data 64-bit")

Configure the DSN and provide the IP address and Port as listed under "QRemote Server." 

Under the QRemote for QuickBooks – Client  DSN configuration screen, Please fill in the following information 

·         Data Source Name: Name of your DSN entry 

·         Server: IP address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

·         Encryption Key/Password: (By Default, Encryption Key/Password is set to "QODBC#QRemote$1". In case you want to change your Encryption Key/Password, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

·         Encryption(By Default, Encryption is set to Off. In case you want to use encrypted data transfer between QRemote client and QRemoteServer, You can configure encryption on QRemoteServer and use the same on QRemote Client DSN) 

·         Remote DSN: select "QuickBooks Data "DSN from the list. ( Make sure QRemote Server is running)  

Start Your QuickBooks.

Run MS SQL 2005 Dev / 2008 / 2012 / 2016 (64-bit) and Login

Run SQL Server Management Studio. You'd better choose SQL Server Authentication and input the right name and password.

Under SQL Server Management Studio , Navigate to "Server Objects" -> Linked Server  -> New Linked Server.

 OR

Provide the "New Linked Server" information: Insert your desired Linked Server Name, select provider Microsoft OLE DB Provider for ODBC Drivers from the provider list & insert "QODBC" as the Product name & "QuickBooks Data 64-bit QRemote" as the Data Source & click "OK" button. 

On Clicking OK, it will start establishing a connection to QuickBooks. Please accept the certificate for allowing data access on QuickBooks.

  

After you have accepted the certificate, the Linked server will be shown under the "Linked Server" Category.

 Configure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers)

   The OLE DB provider options for managing linked queries can be set in SQL Server Management Studio. In Object Explorer, right-click the provider name and select Properties for MSDASQL.

The 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 reviewed 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/QRemote is ready to use with SQL Server/Linked Server

Execute below query

Select * from QRemote…Company

 On each query, QRemote Server Logs the events. If you are facing any issues or delays in response, please refer QRemote Server Event Logs for details on which stage it's taking more time.

Another Test

Select * from QRemote…Account

 

or

 

Select * from OpenQuery (QRemote, 'select * from Account')

 

Note: If your SQL statement contains a where clause, use OpenQuery. When using OpenQuery 'where' clause (filters) will be sent to QODBC, and data retrieval will be faster.

 

 

On each query, QRemote Server Logs the events. If you are facing any issues or delays in response, please refer QRemote Server Event Logs for details on which stage it's taking more time. 

Errors and Solutions 

"[QODBC] QODBC  Server Edition License Required."

The QODBC CDKEY License you have activated is not of the Server edition. Please get in touch with our Sales or Support teamhttp://support.flexquarters.com/esupport/

"[QODBC] Evaluation Expired" OR "Evaluation Expired. QRemote requires a QODBC license. Please purchase QODBC or QRemote license."  

You have run out of the 30-day trial/evaluation version or are using QODBC Read Only Edition for QuickBooks Enterprise. If you still wish to evaluate QODBC, please get in touch with our Sales team.

"QRemote" or "QODBC" contains multiple tables that match the table "TableName."

OR

An invalid schema or catalog was specified for the provider "MSDASQL" for the linked server "QRemote."

This means you have not configured the "MSDASQL" provider. Please refer above steps and configure the "MSDASQL" provider.

 

Problem Description:

I have the read-only version included in the enterprise product. I can connect from the VB demo and Excel but not from the SQL server (I always cannot open Reckon Accounts). All I want to do is export data to the SQL server.

I have made the SQL server process log on as the same windows user as Reckon Accounts and QODBC. I am not running RA, QODBC, or SQL server as a windows administrator, and I have UAC turned on. Often during the query, a box pops up trying to start:
c:\Program Files (x86)\intuit\Reckon Accounts 2015\DBManagerExe.exe StartServer 64


It pops up twice, and I choose yes. I have also tried to run it manually.
c:\Program Files (x86)\intuit\Reckon Accounts 2015\DBManagerExe.exe StartServer 32.
 

Solution:

Please restart your machine & please run Reckon, log in to your company file with Admin user & try to connect using your application.

To use DCOM Server & SQL Server with QODBC, you need a QODBC Server Edition license.

Please use the QRemote Server instead of the DCOM Server to connect to the SQL Server using QODBC. Please get in touch with your QODBC dealer to purchase the QODBC Server Edition license.

For licensing detail, please refer: to QODBC with QRemote Licensing Information.

Please Refer:

Using QuickBooks data with MS SQL Server using QODBC Driver and QRemote (Video)

QODBC Reports Architecture Mismatch Problem When connecting

Keywords: SQL Server 2008 Management Studio, SQL Server 2016 Management Studio, SQL Server 2012 Management Studio, SQL Server Management Studio, SSMS

(5 vote(s))
Helpful
Not helpful

Comments (14)
Jermiane gray
04 September 2014 06:02 AM
Hello Support how do i insert data into quickboks from sql using qodbc. am getting this error when trying to do so.

INSERT INTO QODBC...item
(ListID, CustomFieldUPCCode, CustomFieldSerialNumber, Description, FullName)
SELECT ItemID, UPCCode, SerialNumber, Description, ItemName
FROM TMSItemMaster
Jack
04 September 2014 08:11 AM
Hi Jemaine,

I would like to inform you that Item table is read-only table, you cannot insert record in Item table. You can insert record ItemInventory, ItemNonInventory, ItemOtherCharge & on other Item table depend on Item type.

Please refer below mentioned article for add an Inventory Item using QODBC:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/1135/50/how-to-add-an-inventory-item-using-qodbc

Also, I noticed you are trying to insert non insertable field (i.e. ListID) in your query. There are few columns, values for which automatically generated by QuickBooks SDK and are not allowed during Insert/Update. Example ListID or TxnID

You can get more information about whether a column is allowed during Insert/Update or not, by typing below command in VB Demo:

sp_columns TableName

For Example: sp_columns ItemInventory

The command will return the schema rules of selected table (In this example it is ItemInventory), including information about whether a column is updatable or insertable.

I would like to inform you that you cannot directly insert custom fields in QuickBooks using QODBC.

In QODBC user can only update a custom field in the table. Insert with a custom field is not supported for now. Our developers are working to make this feature available in QODBC.

So you can update existing records to assign a value to custom field. But if you want to create a new record with Value in a custom field, you need to first create it without custom field value, then update the newly created item.

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
Shawn
04 December 2014 08:43 PM
Great directions. I was able to get it to work, however, I would like to do more: how do you make multiple QB linked servers on the same SQL Server accessible at more or less the same time?

I was able to set up two linked servers, QB1 and QB2. I then noticed some interesting behavior through the following steps:

1) Execute select * from qb1...company executes successfully.
-QBW32.EXE remains running in the task manager

2) Execute select * from qb2...company fails with the following message: OLE DB provider "MSDASQL" for linked server "qb2" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "qb2".

3) After some time (1-3 minutes) QBW32.EXE stops running (as observed through task manager).

4) Execute select * from qb2...company executes successfully.
-QBW32.EXE remains running in the task manager

5) Execute select * from qb1...company fails with the following message: OLE DB provider "MSDASQL" for linked server "qb1" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "qb1".

6) After some time (1-3 minutes) QBW32.EXE stops running (as observed through task manager).

7) Execute select * from qb1...company executes successfully.
-QBW32.EXE remains running in the task manager

As you can see, QB only seems to allow me to run one instance of QBW32.EXE at a time. Is there a way around this? I thought I should be able to do so because I have Enterprise edition.

Thank you in advance for your help!

Shawn
Jack
09 December 2014 09:12 AM
Hi Shwan,

I would like to inform you that there is a limitation with the QuickBooks SDK, that it can connect only 1 company file at a time on a machine. QODBC using QuickBooks SDK & QRemoteServer uses QODBC thus you will able to access one company file at a time on a machine. If you want access other company data, then you need to close previous company & open other company file in QuickBooks. So you can access other company file data.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn’t expose one feature to application in SDK, QODBC could not do it either.
Tony Wen
28 May 2015 05:15 PM
I found this information on your website http://www.qodbc.com/qodbcsqlserver.htm
Creating a linked table in SQL Server

Running QODBC as a link server requires the Web Server Edition of QODBC and assumes QuickBooks running on the system console.

I have check my QODBC version, it is a QODBC Desktop Read Write 2014 version 15.0.0.307. Can this edition support SQL linked server?

I have difficulties to setup the linked servers and import data from QB to SQL. So I'd like to confirm whether my QODBC supports it.
Thanks for your help

Tony
Jack
29 May 2015 09:48 AM
Hi Tony,

I would like to inform you that you when the machine using the services likes a webserver, PHP MS SQL Server and connecting to QRemote/QODBC need a server license. If connecting remotely, one which runs such services needs a Server License.

QODBC Desktop Read Write does not work with SQL Linked Server. You need to purchase/upgrade license to QODBC Server Edition licese.

Please get in touch with our sales team to purchase license:
http://qodbc.com/contact.htm

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.

1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
naresh
17 April 2017 07:56 AM
i have created database i want to display data into quickbooks from sql server 2014.
Jack
18 April 2017 07:17 AM
Hi Naresh,

If you want to show your SQL Server data into QuickBooks, then you need to write the query to insert your SQL Server data into QuickBooks.

For Example:
If you want to display Invoice data into QuickBooks from SQL Server, then you need to write the query for generating Invoice which will insert data into QODBC Linked Server table from your local database.

Please refer below mentioned article for creating Invoice using QODBC:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2389/0/how-to-create-invoices-using-qodbc
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2582/45/qodbc-desktop-how-to-create-invoices-through-ms-access-using-qodbc

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Tim Taylor
05 September 2017 08:20 PM
I am running SQL Server 2012. I am trying to run your test query
Select * from qremote…company
and am getting invalid syntax. Please advise.
Jack
06 September 2017 10:35 AM
Hi Tim,

Please make sure that your Linked Server name is "qremote". If you have created Linked Server name with the different name, then you need to use actual Linked Server name.

For Example:

Select * from YourLinkedServerName…TableName

If your Linked Server name is QRemote & you want to query Company table, then please use below sample query.
Select * from QRemote…Company

If you are still facing the issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Cheryl Mclaughlin
13 December 2017 08:40 PM
Is it possible to insert data into quickbooks invoice table from sql using qodbc.?
Jack
14 December 2017 09:49 AM
Hi,

Please refer below mentioned link for How to create Invoices using QRemote from SQL Server:
https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2651/44/qodbc-desktop-how-to-create-invoices-using-qremote-from-sql-server
Abra
09 March 2018 02:29 PM
If I have Quickbooks on one machine and SQL Server on another does qremote server need to be installed on both? From the video it seems we just need qremote server on one machine and QODB server edition on the SQL machine. Can you clarify?
Jack
12 March 2018 11:45 AM
Hi Abra,

QODBC license depends on the application which you want to use. I would like to inform you that you when the machine using the services like a web server, PHP MS SQL Server and connecting to QRemote/QODBC need a server license.

So If you are using any service-based application, then you need QODBC Server Edition license on application machine & QODBC Desktop (QRemote License) on QuickBooks machine.

You can access QuickBooks Data remotely by installing QODBC on both machines (i.e. On QuickBooks application server & on your machine).

If your QuickBooks application is installed on another machine, then you can connect to QuickBooks from a remote machine using QRemote Server. You need to install QODBC on both machines.

You need to run QRemote Server on QuickBooks machine & try to connect from your workstation using QRemote Client. QRemote Client should point to QuickBooks Server IP & Port.

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).