Quickbooks - QODBC - Crystal Reports 2008 Issues

Titlevest
Titlevest used Ask the Experts™
on
We currently are experiencing some difficulties with using QODBC to connect CR2008 and QB8. All tables besides the Invoice and Invoiceline tables work perfectly. One of the reports created accesses all invoices not paid, QOBDC only returns one entry from 2006.

I moved the QB file on my personal computer (at first attributed the issue to insufficent resources on the server) and received the same results. I ran the verify and rebuild utility using my computer (Windows XP) and was able to run the report perfectly once complete.

After moving the company file back to the server, the next morning the issue reoccured. At this point I am not sure what is causing this issue. Any help with this matter would be greatly appreciated. Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Once you know it is working locally and properly, the only difference appears to be the server connection. Make sure you have properly installed the QB Server Manager on the server and that you have scanned for the ledger file and make sure the ledger lives on the same physical drive as the server manager. Make sure you are using the same version of server manager as QuickBooks. Then make sure you are mapping to the ledger folder properly (Tools -> Map Network Drive; or NET USE commands). ... Thinkpads_User
Commented:
Find the optimizer (filename.OPT) file that the ODBC driver creates and delete it. This means that the next time you run the report, it will take a bit longer, but it sounds like the file has become corrupt. Technically, there should be one .opt file one every system and for every user that runs the report, but it depends on setup. You can check for the default location in the Configure ODBC Driver for QB window under the optimizer tab or just do a search for *.OPT. The driver will recreate a new file next time the report runs.

Author

Commented:
Thinkpads_User and stilnae,  Thank you so much for your replies.

Thinkpads_User, I am sorry, I should have clarified the situation.  Although the quickbooks company filed is stored on the server and our accounting group uses a network connection, the QOBDC process is iniated locally (on the server).  The users connection doesn't play into the process (unless RDP is an issue?)

stlinae, I will try your approach later this afternoon.

Thanks again!
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
I deleted the optimization file and changed the setting for updating the file to "update with every new connection (load updated data first)".  The process is working but of we are missing roughly $140,000 in invoices.  We did a comparison with a QB export and see most of the missing invoices are from 2008 and back.  I change the setting to reload the data with each new connection to see if this takes care of this issue.  I also found this error yesterday in the QODBC log, any thoughts?:

2010-02-01 10:05:02 QODBC Ver:  10.00.00.265 *********************************************************************************************************************
IsAService: False
SQL Statement: SELECT * FROM "Invoice" CALLDIRECT
Error Getting XML in StopIterator
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="7.0"?>
<QBXML>
    <QBXMLMsgsRq onError="continueOnError">
        <InvoiceQueryRq requestID="1" iterator="Stop" iteratorID="{08592217-4231-48a9-8d17-83aa70f09e27}">
            <MaxReturned>00001000</MaxReturned>
            <IncludeLineItems>1</IncludeLineItems>
            <IncludeLinkedTxns>1</IncludeLinkedTxns>
            <OwnerID>0</OwnerID>
        </InvoiceQueryRq>
    </QBXMLMsgsRq>
</QBXML>
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
I cannot help with this particular error. Have you called Crystal Reports Support?
... Thinkpads_User

Commented:
I think theres a setting you can adjust, but for the life of me I can't remember where. While I look into it, try changing your selection critera to only include Invoices with TxnDate <=12/31/08 and see what happens. It might just be that you have to much data thus the <MaxReturned>00001000</MaxReturned> line.

Author

Commented:
It took quite a bit of time for the process to complete (a little over 2 hours), but configuring QODBC to reload the data with every new connection worked.  All of the data exported with the pervious criteria, so I guess it was a corrupt optimization file as stilnae suspected.  Thanks guys!

Author

Commented:
Also configure QODBC to reload data at the start of a new connection.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial