You need to try simple things first like:

SELECT * FROM QODBC...HOST

or

SELECT ListID, Name FROM QODBC...Customer

See: Does QODBC work with MS SQL Server Linked Tables? for more.

OK. So I've managed to get it to query, but only if QB is running, which is Problem #1.

But Problem #2 is that even though the query completes without errors, no data is returned. To recap my setup:

• SQL Server 2005 SP2
• SSMS 2005 running on same server
• QB Pro 2006 US
• Latest QODBC in DCOM mode (all DCOM tests pass)

This is how I created the Linked Server that worked:

EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ= ;SERVER=QODBC;UseDCOM=Y; OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer; OptimizerAllowDirtyReads=N'

This is the query that worked:

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

This variation DOES NOT WORK:

SELECT ListID, Name FROM QODBC...Customer

returning the error message 'An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "QODBC".' What is strange is that the VBDemo works. What is the difference in the way it negotiates permissions? Has QODBC been thoroughly tested with SQL 2005?

Also, why won't either the VBDemo or the SQL query work if QB is closed? All DCOM settings and QODBC settings are as specified in the above link, and permissions have been granted on the company file correctly.

Try:

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

To run without QuickBooks being opened you need to setup unattended mode as per: How do I setup QODBC to auto-login into QuickBooks and start it if it's not running? 

Does:

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

return one rowset?

Note: You may need to check the "Run 2.1 ODBC Compatibility" checkbox - Certain applications require ODBC 2.1 Compatibility to use QODBC. For example, anything that uses the Java ODBC library, like ColdFusion, requires this option turned on or recordsets will be returned with no rowsets or only 1 rowset.

Try switching over to using the Rock Castle sample company file and see if that makes a difference. Check that VB Demo works and that rows are returned, and in the QODBC Setup Screen check the license status in "About" and if there are error messages in the QODBC and SDK log files in the "Messages" tab.

The DCOM method only works if you have a QODBC Server Evaluation or fully pruchased Server license, but it will work with any of the QuickBooks Sample Company files if you don't.

No, the Rock Castle file behaves identically--the query runs but returns no records in SQL Server (and will not run using the SELECT * FROM QODBC...Host syntax either), and runs correctly in the VBDemo. Here are the qbsdklog results for the SQL run and the VBDemo run, respectively:

-------------------------------- BEGIN LOG --------------------------------
SQL Server
20070423.103039 I 1276 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3584). 20070423.103039 I 1276 SpecVersion Current version of qbXML in use: 5.0
20070423.103039 I 1276 QBSDKMsgSetHandler QUERY: Host
20070423.103039 I 1276 QBSDKMsgSetHandler Request 1 completed successfully.
20070423.103039 I 1276 QBSDKMsgSetHandler QUERY: Company
20070423.103039 I 1276 QBSDKMsgSetHandler Request 2 completed successfully.
20070423.103039 I 1276 QBSDKMsgSetHandler QUERY: DataExtension Definition
20070423.103039 I 1276 QBSDKMsgSetHandler Request 3 completed successfully.
20070423.103039 I 1276 MsgSetHandler Finished.
20070423.103039 I 1276 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3584), ret = 0.
20070423.103039 I 1276 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3584).
20070423.103040 I 1276 SpecVersion Current version of qbXML in use: 5.0 20070423.103040 I 1276 QBSDKMsgSetHandler QUERY: Host
20070423.103040 I 1276 QBSDKMsgSetHandler Request 1 completed successfully. 20070423.103040 I 1276 MsgSetHandler Finished.
20070423.103040 I 1276 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3584), ret = 0.

VBDemo
20070423.103312 I 1916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3628).
20070423.103313 I 1916 SpecVersion Current version of qbXML in use: 5.0
20070423.103313 I 1916 QBSDKMsgSetHandler QUERY: Host
20070423.103313 I 1916 QBSDKMsgSetHandler Request 1 completed successfully.
20070423.103313 I 1916 QBSDKMsgSetHandler QUERY: Company
20070423.103313 I 1916 QBSDKMsgSetHandler Request 2 completed successfully.
20070423.103313 I 1916 QBSDKMsgSetHandler QUERY: DataExtension Definition
20070423.103313 I 1916 QBSDKMsgSetHandler Request 3 completed successfully.
20070423.103313 I 1916 MsgSetHandler Finished.
20070423.103313 I 1916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3628), ret = 0.
20070423.103317 I 1916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3628).
20070423.103317 I 1916 SpecVersion Current version of qbXML in use: 5.0 20070423.103317 I 1916 QBSDKMsgSetHandler QUERY: Host
20070423.103317 I 1916 QBSDKMsgSetHandler Request 1 completed successfully. 20070423.103317 I 1916 MsgSetHandler Finished.
20070423.103317 I 1916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3628), ret = 0.
-------------------------------- END LOG --------------------------------

Looks identical to me. So it's got to be a QODBC problem. Once again, I am using:

• QODBC 7.0.0.214 evaluation (in server mode)
• Latest QB SDK (installing this fixed the access-while-company-file-closed problem, btw)
• Latest QBXML
• QB Pro 2006 US, all updates installed
• SQL Server 2005 SP2

We would need to see a trace file to really see what's happening here.

Please run:
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Host')
SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Customer')
via SQL Server and
SELECT * FROM HOST
SELECT ListID, Name FROM Customer
in VB Demo, then zip it up and send the trace file to: qdeveloper@qdeveloper.com.au

See: How do I turn on the trace log? 

Using the sample file, what happens if you do?:

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

I know you like your stored procedure:

EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ= ;SERVER=QODBC;UseDCOM=Y; OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer; OptimizerAllowDirtyReads=N'

but can you please manually link QODBC as per: Does QODBC work with MS SQL Server Linked Tables? 

Ok, simply turn off the QODBC optimizer for now.

You had said SQL Server 8.0 SP3 which is SQL Server 2000, but the 2005 server instructions are at: Does QODBC work with MS SQL Server 2005 Linked Tables? 

If you unlink and relink with the optimizer turned off using:

EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ= ;SERVER=QODBC;UseDCOM=Y; OptimizerOn=N

it should work correctly. The problem here is most likely related to your connection settings overwritting the QODBC setup settings. The optimizer is also single user, so you can't have SQL Server using it the same time as VB Demo.

You can use the optimizer, but I think you need to actually state the path to a unique instance of the optimizer, for example:

EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ= ;SERVER=QODBC;UseDCOM=Y; OptimizerDBFolder=C:\Documents and Settings\dirving\Application Data\QODBC Driver for QuickBooks\Optimizer; OptimizerAllowDirtyReads=N'

Using the optimizer with MS SQL Server can be trickly depending on how MS SQL Server is setup in regards to using authenticated users. The actual connection to QuickBooks normally uses the Windows admin account, but when logged into a Windows user account this gets to be the account logged in. Therefore you should always use the Windows primary "Administrator" admin account to log into the server and use QODBC.

A linked server runs queries as muliple treads, and 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 disktops applications. To do this step 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 (off of the root of C: drive) instead of the default UserProfile method.

Then login as the primary Windows "Administrator" account and try again. Services are mean't to use the admin account and not Windows user accounts. The issue here is that the QODBC DCOMs and the services that call them are all really designed to be run in the "Administrator"s console.

Note: You may need to create a new System DSN that includes an optimizer file off the root of your C: drive to successfully use the QODBC Optimizer with MS SQL Server 2000.

See the last post in SQL Server 2005 Linked Server unable to run Optimized  for further clues.