Running QODBC as a Linked server requires the Server Edition of QODBC. QuickBooks must be running with the company file opened on the system console. The QODBC Server Edition includes DCOM servers required to allow QODBC running as a system service to find the instance of QuickBooks running on the server console.
 
You have two choices in setting up QODBC Linked Tables with Microsoft SQL Server. The first is using the QODBC DCOM Servers. The second is using the Remote Connector Option.

The DCOM Servers require QuickBooks to be running on the Server Console (Local session), not a Terminal Services session. They are more secure and require no extra overhead but are more rigid on how it is setup and it can be difficult to configure the security of the DCOM servers.

The Remote Connector option allows the window that is running QuickBooks to be anywhere that is accessible via TCP/IP. That means QuickBooks can be run in a Terminal Sessions window, on another computer on your LAN or anywhere on the WEB. If your data will be transmitted over the internet we recommend you upgrading the Remote Connector to the SSL secure version. The instructions on setting up the Remote Connector version are found in Using QODBC Remote.

QODBC Basic Setup For Using the DCOM Servers Method
You must turn on use of the DCOM servers. You do this by running the QODBC Setup Screen and selecting the "Server Edition" tab page. Check the checkbox for "Use DCOM Servers".

Automatic DCOM Configuration
Click here: qodbcSetDCOM.zip to download the following automated tools:-

• Dcpinst.exe is an installation setup for the automated DCOM configuration object. This needs to be run first and only once.
• QODBCSetDCOM.VBS is a VB script that uses the installed configuration object to setup the DCOM Servers. It will reset and automatically add all necessary security each time it is run. The script can be edited if you need to change the Accounts it is configuring.
• The new settings will not take affect until the DCOM servers (qbXMLRPeQODBCInteractive.exe and qbXMLRP2eQODBCInteractive.exe) are removed from memory. Rebooting is the simplest way to accomplish this, but running the Windows Task Manager and Ending Process on any running copies will also work.
• Test your DCOM setup using one of the following:-
  • IIS users download: TestDCOM.ASP
  • PHP users download: TestDCOM.PHP
  • Cold Fusion users download: TestDCOM.CFM

Setting up "Auto-Login" Unattended Mode
Locate the "QODBC Driver for QuickBooks" program group on your desktop. Click on the "QODBC Setup Screen" icon to launch the setup program supplied with QODBC.

Creating a Linked Table in Microsoft SQL Server 2005
You must create a system DSN, by default the DSN "QuickBooks Data" is created for you when QODBC is installed, but you can use any system DSN created for QODBC. Using Enterprise Manager, select your SQL server->Security->Linked Servers then right click (or Action menu) and select "New Linked Server...". The following screen shot below shows a simple Link Server setup for QODBC:

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

Run a query. Below are a few examples based a Link Server named "QODBC":-

• SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Host')

• SELECT * FROM QODBC...HOST

• SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM HostSupportedVersions')

• SELECT * FROM QODBC...HostSupportedVersions

• SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Employee')

• SELECT ListID, Name FROM QODBC...Employee

• SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Customer')

• SELECT ListID, Name FROM QODBC...Customer

• SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name, CompanyName FROM Vendor')

• SELECT ListID, Name, CompanyName FROM QODBC...Vendor

• SELECT * FROM OPENQUERY(QODBC, 'SELECT Name, CompanyName FROM Vendor Where "Name" =''Bayshore Water''')

• SELECT * FROM OPENQUERY(QODBC, 'sp_report CheckDetail show TxnType_Title, RefNumber_Title,Date_Title, Name_Title, Item_Title, Account_Title, PaidAmount_Title, OriginalAmount_Title, TxnType, RefNumber, Date, Name, Item, Account, PaidAmount, OriginalAmount parameters DateMacro = ''ThisMonthToDate''')

• UPDATE OPENQUERY(QODBC, 'SELECT "Name", "CompanyName" FROM Vendor WHERE "Name" = ''Bayshore Water''') SET "CompanyName" = 'Bayshore Water, LLC'

• UPDATE QODBC...Vendor SET "CompanyName" = 'Bayshore Water, LLC' WHERE "Name" = 'Bayshore Water'

• INSERT INTO QODBC...Customer (Name) Values ('Test Customer 201')

• sp_linkedservers

Trouble Shooting QODBC as a SQL Linked server
Below is an SQL script that will test the DCOM Server setup. All components should have a description of "Successfully Created" when everything is properly configured. Follow the instructions on this web site for the Web Server Edition if you have difficulty with DCOM configuration.

Test DCOM Script (.vbs):

My understanding is that SQL Server 2005 does not have the property menu item on the Link Server providers like SQL Server 2000. 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. If you can't configure them through the UI as indicated in the directions you can script this action like this:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DynamicParameters', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'IndexAsAccessPath', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'LevelZeroOnly', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NestedQueries', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NonTransactedUpdates', 1
GO

But you can also run the query directly without OPENQUERY like this:

SELECT ListID, Name, CompanyName FROM QODBC...Vendor

When this didn't work, I also found that it was because my Ole Automation Procedures Option was turned off in my SQL Server Surface Area Configuration.

To turn it on with a query, see:
http://msdn2.microsoft.com/en-gb/library/ms191188.aspx

which contains:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

See: Using DTS to Load QuickBooks Data into Microsoft SQL Server for more on how to transfer all the QuickBooks tables.

Hi again,

I have followed these steps to set up my DCOM server and Linked Server in Management Studio.  However when I perform the first test query [SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Host') ], I get the column headers but no data.   If I do the query SELECT * FROM OPENQUERY(QODBC, 'SELECT count(*) as HostCount FROM Host'), I get the HostCount header but no data.

Management Studio reports the query as successful.  Doing these same queries from VB Demo works fine.

Thanks for any help!

_Mike

Some more details about the problem reported above: I *AM* running from the console, logged into QB as Admin in Multi-user mode and Management Studio responds to the query with "(0 row(s) affected)"

Thanks.

I find my problem getting stranger.  After taking a lunch break a complex query worked perfectly and returned 18 rows.  I then tried a simple query and got nothing and retried the complex query and then got nothing from that too.

Any ideas?

_Mike

Most likely you are using VB Demo and Management Studio at the same time. The QODBC optimizer is single user only so you need to setup a different optimizer file for the SQL Server to use than the other desktop applications. To do this, set up a seperate System DSN (see: How do I create or configure a QODBC DSN? ) and specify a fixed location of the optimizer database folder for SQL Server to use (like: C:\Program Files\QODBC Driver for QuickBooks) instead of the default %UserProfile% method.

To repair a optimized table (for example the Invoice table) you can run:

SELECT * FROM OPENQUERY(QUICKBOOKS, 'SELECT * FROM Invoice VERIFY WHERE TxnID = "x"')

Another way of doing this is to simply log in as the account that the SQL Server Agent runs under. Bring up the QODBC Setup Screen and under the OPTIMIZER tab click "reload all data" - at this point you will have <companyname>.opt file in the folder.

I am having exactly the same problem as Mike. I am NOT running the VB demo simultaneously. I set up a separate DSN and optimizer folder. This does not help. I am able to use the DTS wizard to populate tables. However, when I query the Quickbooks linked server I get a successful execution but 0 rows returned. I have followed all of the outlined steps given on the forums.

In my "ODBC Data Source Administrator" driver list I have the two drivers " QODBC Driver for Quickbooks" and "QODBC OEM Driver N"

Try checking "Run 2.1 ODBC Compatibility" if it's unchecked, or unchecking it if it's already checked.

Delete the "QODBC OEM Driver N" from your QuickBooks Integrated Applications list as it doesn't work with Windows Services like MS SQL Server 2005.

The "QODBC OEM Driver N" does not appear in Quickbooks Integrated Applications. However, I removed it from the Windows Registry. I unchecked "Run 2.1 ODBC Compatibility".  I get the same results for

SELECT * FROM QBLS...Host

or

(0 row(s) affected)

The ODBC Message log is blank. When I use SQL server no messages are generated. The trace log also remains empty.  If I use the VB Demo to run a query, the trace log is not empty.

Also, when I do an insert from Management Studio, it says that it is successful and that 1 row is affected, but nothing appears in Quickbooks.

The Quickbooks company file, Dummy.QBW, is new. The only thing that I have added is one customer.

ISAMOpen
    Database: C:\QBcompany\Dummy.QBW
    DSN: QBDSN
    UserName:
    Password:
    DeveloperCode:
    ApplicationCode:
    OpenMode: M
    UseDCOM: Y
    IBizRemoteConnection: N
    IBizRemoteURL: http://localhost:2080
    IBizRemoteUserName: admin
    IBizRemotePassword:
    IBizRemoteAskPassword: Y
    IBizRemoteSSLAcceptServerCert:
    IBizRemoteConnectionTimeOut: 120
    IBizRemoteForce2002_2003: N
    IBizOEConnection: N
    IBizOETicketGUID:
    IBizOELoginGUID:
    IBizOELoginAsk: Y
    IBizReconnectionDelay: 0
    ColumnNameLen: 50
    UseCaseSensitiveRefNumber: Y
    OptimizerOn: Y
    OptimizerDBFolder: C:\QBcompany\QBDSNOptimizer
    OptimizerCurrency: C
    OptimizerDirtyReads: D
    OptimizerUpdateSync: Y
    OptimizerEncrytionOn: Y
    UseIterators: Y
    IteratorChunkSize:  1000
    IAppLaunchQBUI: Y
    IAppAutomaticLogin: Y
    IAppReadOnly: N
    IAppAllowPersonalData: Y
    ConvertToLongVARCHAR: FALSE
    MaxLONGVARCHAR:  4096
    Convert To LONGVARCHAR: N
    Max LONGVARCHAR:   4096
    OleInitialize - Returned OK
    Logged In User:    John
    Is a Service:      No
    Registration Name: Expressions Unlimited, Inc.
    RegSerial:         S07U10016977
    QODBC Version:     7.00.00.214
    DLL:               C:\WINDOWS\system32\fqqb32.dll
    Driver Name:       FQQB32.DLL
    Process Count:     1
    Thread Count:      1
    OpenMode:          Multi-User Mode
    Include Raw:       Yes
    Version Type:      QODBC Server Edition 2007
    Access Method:     QBXMLRP2EQODBCInteractive.exe
    Thread ID:         4904
    SDK Module:        QBXMLRP2EQODBCInteractive.exe
    QB Company Name:   C:\QBcompany\Dummy.QBW
    QB Product Name:   QuickBooks Enterprise Solutions: Manufacturing and Wholesale Edition 7.0
    QB Major Version:  17
    QB Minor Version:  0
    QB File Mode:      MultiUser
    QB Automatic Login:Yes
    QB Country:        US
    QB Supported Ver:  1.0
    QB Supported Ver:  1.1
    QB Supported Ver:  2.0
    QB Supported Ver:  2.1
    QB Supported Ver:  3.0
    QB Supported Ver:  4.0
    QB Supported Ver:  4.1
    QB Supported Ver:  5.0
    QB Supported Ver:  6.0
    Optimize SQLITE3 Library Version: 3003007
    Optimize File Contents: C:\QBcompany\QBDSNOptimizer\Dummy.OPT

Please turn off the "Include Raw Files" setting in QODBC Setup Advanced Tab screen and make sure you are logged in as the administrator on the server (not John).

Tom was right that I was running VB Demo and SQL Server at the same time through the same connection.  That's what started the problem.  However closing VB Demo didn't fix it.  I found that my .OPT file had become corrupted by opening it in SQL Server and VB Demo at the same time.  I suggest that you close all QODBC connections, delete your .OPT file and rebuild it.

Cheers,

Mike

I deleted the .OPT file and did "rebuild all data". I also tried starting with no .OPT file and letting it be built as I ran queries from Managemment Studio. "John" is the Windows Administrator and I am using Windows authentication in SQL Server 2005 Express.

I am now getting a trace file from using SQL Server. 

I increased the iterator chunk size. Why isn't there a line that says "QB Supported Ver:  7.0"?
Should I completely uninstall Quickbooks 7.0 and SQL Server 2005 Express and reinstall them?

My DCOM test results are:

QODBC ASP DCOM Access Test Page

Current Logged on User is:
Authenticated user is:

QB2002	QB2003	QB2004	DCOM	COM	Object Name	Status
•	•		•		QBXMLRPEQODBCInteractive.exe	Success
•	•			•	QBXMLRP.dll	Success
		•	•		QBXMLRP2EQODBCInteractive.exe	Success
		•		•	QBXMLRP2.dll	Success
•	•	•		•	XERCES-COM.dll	Success

When I run a query, I can see SQL Server writing to the .OPT file.

Ok, do you get a row if you run:

SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Host')

If not run:

SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Host unoptimized')

It was a wonderful feeling! I entered the second query, and a row was returned.

However, the queries only work when I use OPENQUERY and the UNOPTIMIZED flag.

For instance, this query does not work:

SELECT * FROM QBLS...Host unoptimized

The UNOPTIMIZED tag tells QODBC to call QuickBooks directly bypassing the QODBC optimizer. So let's try this:

SELECT * FROM OPENQUERY(QBLS,'sp_optimizefullsync Customer')

SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer')

When I run this query:

I get this error message

Do a complete rebuild instead by doing "Reload All Data" while logged in as John using the QBLS DSN.

See also: How do I start the QODBC Setup screen to use my DSN instead of QuickBooks Data? 

Do:

SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer')

and if it doesn't return any rows:

SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer unoptimized')

Only the second query returns rows.

When I run the DCOM TEST SQL script in Management Studio, I get the following result:

dbo Jul  8 2007 11:34PM

QBXMLRPEQODBCInteractive.RequestProcessor CreateObject 0x80070005 ODSOLE Extended Procedure  Access is denied.      

QBXMLRP.RequestProcessor CreateObject 0x00000000  Successfully Created

QBXMLRP2EQODBCInteractive.RequestProcessor CreateObject 0x80070005 ODSOLE Extended Procedure  Access is denied.

QBXMLRP2.RequestProcessor CreateObject 0x00000000  Successfully Created

Xerces.DOMDocument CreateObject 0x00000000  Successfully Created

But when I run the TestDCOM.asp, I get "success" for all.

That worked for the SQL sqript DCOM test. I get "successfully created" for all. I have given full control to the C: drive and all of its subfolders to: INTERACTIVE, ASPNET, IUSR_<computer_name>, AUTHENTICATED USERS.

I am now able to get rows without the 'unoptimized' tag.  When I run this query,

OR

I get the rows, however. If I run this query, I get this error.

I wanted to mention that I started to get the rows returned after I logged onto SQL Server as 'sa'.

When i Run this update query

I get this error

See: How do I do INSERTs, UPDATEs and DELETEs using OPENQUERY with Linked MS SQL Servers? 

'sp_optimizefullsync Customer' doesn't return any rows and cannot be run directly through a linked server. A workaround would be:

SELECT * FROM OPENQUERY(QBLS, 'SELECT FullName FROM Customer VERIFY where FullName = ''x''')

For Update Sync:
SELECT * FROM OPENQUERY(QBLS, 'SELECT TxnID FROM InvoiceLine OPTIMIZE WHERE TxnID = ''x''')

And for Full Sync:
SELECT * FROM OPENQUERY(QBLS, 'SELECT TxnID FROM InvoiceLine VERIFY WHERE TxnID = ''x''')

When I run this query, there is no change in the .OPT file (i.e. modified timestamp or the file size)

That depends on your "Display Optimizer Status Panel" setting, however calls through a service don't get the focus and therefore are often not seen on the desktop, are minimized or appear underneath other elements.

The timestamp does not change on the .OPT file. It is still the timestamp of  when the "rebuild all data" completed. I have closed the service and restarted my computer.

Note: QuickBooks only allows for one Company file to be open at the one time. To link mulitple company files you will need to link and unlink seperate company files by specifing different paths in the DFQ=fullpathname connection parameter with a delay between executing sp_addlinkedserver and sp_dropserver.

For example:

EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ=C:\Program Files\QODBC Driver for QuickBooks\sample04.qbw;SERVER=QODBC;UseDCOM=Y; OptimizerDBFolder=C:\Program Files\QODBC Driver for QuickBooks\Optimizer; OptimizerAllowDirtyReads=Y'


EXEC sp_dropserver @server = N'QODBC'

These stored procedures also allow you to specify the location of the OptimizerDBFolder.