| Unable to import some tables from Quickbooks using remote connector. |
| Author |
Message |
|
|
| Posted : 2007-11-01 03:15:21 |
I'm currently running the current configuration.
Machine 'A' has Quickbooks 2007 Pro Canadian installed on it along with QODBC and the iBiz connector, all are updated to the most recent releases.
Machine 'B' has Access and QODBC installed on it. The machines communicate via a LAN.
On machine 'A' I can read any of the tables from within Quickbooks without any problems using the VBDemo. I've confirmed that the table I require, the invoice table, does return the proper result set.
On machine 'B' from either Access or the VBDemo I can read some tables for example the HOST and Customer tables without problems but not the Invoice table which is the one I am after. When I query the Invoice table the only result returned is the headers without any records.
I've increased the Connection Timeout to 240 and the Reconnect Delay to 60 but that doesn't appear to have any bearing on the results other than waiting an hour to find out you get an empty recordset.
I do however get the following error message in the log:
2007-10-31 08:23:46 QODBC Ver: 8.00.00.239 *********************************************************************************************************************
IsAService: False
SQL Statement: SELECT * FROM "TxnDeleted" CALLDIRECT
Error Getting XML in BuildAndLoadXMLForNextRecord
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="CA3.0"?>
<QBXML>
<QBXMLMsgsRq onError="continueOnError">
<TxnDeletedQueryRq requestID="1">
<TxnDelType>Bill</TxnDelType>
<TxnDelType>BillPaymentCheck</TxnDelType>
<TxnDelType>BillPaymentCreditCard</TxnDelType>
<TxnDelType>Charge</TxnDelType>
<TxnDelType>Check</TxnDelType>
<TxnDelType>CreditCardCharge</TxnDelType>
<TxnDelType>CreditCardCredit</TxnDelType>
<TxnDelType>CreditMemo</TxnDelType>
<TxnDelType>Deposit</TxnDelType>
<TxnDelType>Estimate</TxnDelType>
<TxnDelType>Invoice</TxnDelType>
<TxnDelType>InventoryAdjustment</TxnDelType>
<TxnDelType>JournalEntry</TxnDelType>
<TxnDelType>PurchaseOrder</TxnDelType>
<TxnDelType>ReceivePayment</TxnDelType>
<TxnDelType>SalesReceipt</TxnDelType>
<TxnDelType>TimeTracking</TxnDelType>
<TxnDelType>VendorCredit</TxnDelType>
<TxnDelType>SalesOrder</TxnDelType>
<TxnDelType>ItemReceipt</TxnDelType>
</TxnDeletedQueryRq>
</QBXMLMsgsRq>
</QBXML>
Any help would be greatly appreciated. |
|
|
|
|
|
| Posted : 2007-11-01 09:35:12 |
| Additionally I ran a SQL query via QODBC on machine 'A' against the invoice table and got the results I was looking for. Now subsequent calls to the invoice table fail. I've researched the forums and have tried some of the presented solutions such as limiting the query to a specific date range, e.g. > {d '2007-05-01}, without any success. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-01 09:40:32 |
|
|
|
|
|
| Posted : 2007-11-02 05:05:05 |
Tom,
Many thanks for your prompt reply. As suggested I've changed the settings as per the link you posted and had to go as far as enabling all permissions. This was successful on one run through and I was able to successfully pull in the invoice data to machine 'b' once. Subsequent attempts after reducing permissions down to the posted link's article to reduce the exposure of sensitive data has failed, and re-enabling all permissions also now fails. I've tested other queries against other tables such as the host and customer tables and those work fine against all permission levels. But I was only able to get one successful test against the invoice table initially.
Do you have any other suggestions? Frankly my boss is looking to rip me a new one because of the amount of time I've spent on this issue.
Thanks,
Tom |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-02 07:07:30 |
Most likely you have broken your optimized table so you need to run this using VB Demo:
select * from Invoice UNOPTIMIZED WHERE Txndate > {d '2007-04-01'}
If that works, resync your optimized Invoice table by doing:
sp_optimizefullsync Invoice |
|
|
|
|
|
| Posted : 2007-11-02 07:48:14 |
Tom,
Okay, that was the solution and thanks for your patience.
One final question though. I can't see this in the docs or the forums. I've set the optimizer to run at the end of the day, but my IT guy is wondering when specifically it runs or if it can be set to run at a specific time at the end of the day. We perform nightly back-ups and he's worried about a back-up being unable to run because of the optimizer file being open or written to at the same time.
Again, thanks!
Tom |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-02 19:16:05 |
The optimizer doesn't run unless a query is preformed. It doesn't run as a scheduled task, so there won't be any backup issues. The end of yesterday option is used for a better balance between speed and data updating and would be the option that works best for most users. It only has to synchronize each table once per day and the data will be current as of the end of the previous day.
See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more.
|
|
|
|
