[QODBC-Desktop] How to create Link Server with MS SQL 2005/2008/2012 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 64-Bit Using QODBC/QRemote

 For 32 Bit Refer: How to create Link Server with MS SQL 2005/2008/2012 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 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.

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

Since a 64bit 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 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 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:   

    (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) 

·         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 (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

 

 OR

Provide the “New Linked Server” information

On Clicking OK, it will start establishing 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 “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.

 

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/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 delay in response, please refer QRemote Server Event Logs for details on which stage its taking more time.

Another Test

Select * from qremote…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 its taking more time.

 

Errors and Solutions 

“[QODBC] QODBC  Server Edition License Required.”

The QODBC CDKEY License you have activated is not of Server edition.  Please contact our Sales or Support team . http://support.flexquarters.com/esupport/

 

“[QODBC] Evaluation Expired”

You have run out of the 30-day trial/evaluation version, If you still wish to evaluate QODBC, Please contact our Sales team.

“QRemote” or “QODBC” contains multiple tables that match the table “TableName

This means you have not configured “MSDASQL” provider. Please refer above steps and configure “MSDASQL” provider

 

Problem Description:

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

I have made the SQL server process log on as the same windows user as ReckonAccounts and QODBC. I am not running RA or QODBC or SQL server as 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 manually run
c:\Program Files (x86)\intuit\Reckon Accounts 2015\DBManagerExe.exe StartServer 32.
 

Solution:

Please restart your machine & please run Reckon, login 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 required.

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

For licensing detail please refer: 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

(3 vote(s))
Helpful
Not helpful

Comments (8)
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
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).