I've developped a data transfer application based on QODBC desktop edition driver. The purpose of this application is to transfer data from QuickBooks to another accounting system based on a SQL server database. Find below a summary of the application architecture.

The presentation/application layer is built with ASP DOT NET. The data layer is built with SQL stored procedures and tables stored in a dedicated database. ASP calls SQL stored procedures which are designed to retrieve referential and transaction data from QuickBooks through the QODBC driver. The referential/transaction data are stored in work tables and then it is transferred to another accounting system database following mapping rules. The application dedicated database is installed on a Windows XP PRO worksation with SQL 2000 MSDE. The IIS server is installed on the same XP PRO workstation. QuickBooks and QODBC driver are also installed on the XP PRO workstation. The other accounting system database is installed on remote Windows 2003 server with SQL 2000 server standard edition. 

Here is an example of SQL statement used to retrieve data from QuickBooks in one of the SQL stored procedure (in the application dedicated database):

set @sql='select FullName,'''' as AcntCode,'''' Descr from openrowset(''MSDASQL'','''+@data_source+''';'''';'''', ''select * from Class'')'

exec(@sql)

@data_source contains the QODBC DSN name. The QuickBooks data file and the DSN are configured to allow accessing the data file when QuickBooks is closed. The QODBC DSN DCOM option has been activated. When the SQL stored procedure is executed manually from Query Analyzer it is working fine. When it is called from ASP, sometimes it is working and sometimes not at all...

Do I have to purchase another version of the driver? Is it mandatory to have the QODBC server edition to use DCOM option?

I know that the QuickBooks SDK does not allow to open multiple data files at the same time. As my transfer application deals with multiple files, is there a mean to explicitely close a QODBC connection to a data file via SQL?

Thanks in advance for your answer.

Best regards.

J-C Roggo

SQL 2000 MSDE is a Windows Service, so anything called through it requires the use of the QODBC DCOMs. Query Analyzer is an application and can use the QODBC Desktop Edition, however the execution of stored procedures within SQL 2000 MSDE requires the QODBC Server Edition.

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.

Thanks a lot for your answer!

So, if I understand well I should use LINKED SERVER and OPENQUERY functions to perform queries on QuickBooks in stead of OPENROWSET?

What do you mean by ..."with a delay between executing sp_addlinkedserver and sp_dropserver."? Should I use the WAITFOR DELAY SQL statement? If so, what should be the delay?

Best regards.

Correct, QODBC only has a forward only cursor, so a lot of ROWSET calls isn't ideal. Direct SQL statement execution is best.  At least a 5 second delay is required to allow time for QuickBooks to close the company file before you start up another connection.