Hi,

I'm trying to access Quickbooks from via a SQLServer Query,

I'm using the OPENROWSET call and this works the first time I use it then fails the second and subsequent times for about 5 mins with the message below - then it works again - it's as if something it timing out and closing the origional connection thus allowing the SQL to execute again.:

2007-05-31 18:35:41 QODBC Ver:  7.00.00.214 *********************************************************************************************************************
IsAService: False
Thread Change Begin Session Failed. S0000 00000 [QODBC] QB Begin Session Failed. Error = 8004041c, An internal QuickBooks error occurred while trying to access the QuickBooks company data file.

I'm not using a linked server.

Here is my SQL:

Select *
From OPENROWSET('MSDASQL',
                'Driver={QODBC Driver for QuickBooks};
                     DFQ=[Fully qualified QB File name on local workstation];
                    OpenMode=M;
                    OptimiserOn=N;
                    OLE DB Services=-2;',
                'Select *
                From invoiceline UNOPTIMIZED
                WHERE (InvoiceLineAmount IS NOT NULL) AND (Subtotal <> 0) AND (RefNumber = ''[ref No]')');

Any Ideas please?

Dave Henderson

Your query needs to do a full search from the beginning to the end of the InvoiceLine table. Depending on the size of your company file this is going to take some time .... even 5 mins. So I'm guessing QuickBooks is busy processing your request. You need to switch the QODBC Optimizer back on, and make sure the optimizer file has a unique path for this application (as the optmized file can't be shared between applications at the same time) and test for RefNumber first.

The correct way to do this is to do a:

Select *
From OPENROWSET('MSDASQL',
                'Driver={QODBC Driver for QuickBooks};
                     DFQ=[Fully qualified QB File name on local workstation];
                    OpenMode=M;
                    OptimiserOn=Y;
                    OptimizerDBFolder=C:\Documents and Settings\unquiepath\Optimizer;
                    OLE DB Services=-2;',
                'Select *
                From invoiceline NOSYNC
                WHERE (RefNumber = ''[ref No]') and (InvoiceLineAmount IS NOT NULL) AND (Subtotal <> 0)');