|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Having Trouble Running On a Network |
| Author |
Message |
|
|
| Posted : 2006-11-02 11:10:29 |
I'm having a problem running QODBC over a network via an Access 2003 database.
Here's the situation:
- Each client machine has Quickbooks v6, QODBC (with individual licenses) and Access 2003 installed.
- The server has Quickbooks installed.
- The Access database resides on the server, is a front-end to Quickbooks specifically for invoice creation, and has all the necessary tables linked for creation of invoices, and is accessed by multiple users at once.
- The Quickbooks company file resides on the server, is in multi-user mode, and is accessed by multiple users at once, in addition to any connections made by QODBC.
- I'm using QODBC (via linked tables) to read data from the Quickbooks to verify choices made in Access (like the company name, terms, tax, shipping method, etc.) and then once all information is verified, I insert records into the invoiceline table as cached records, and then finally insert the invoice itself.
Here's the problem I'm having:
QODBC takes an incredible amount of time to read data from Quickbooks, sometimes fails on inserts into the invoiceline or invoice table, and occasionally it will get through the entire creation process, but will return an empty string when I use the SP_LASTINSERTID stored procedure (even though the invoice is successfully created!). There doesn't seem to be any inherent network latency aside from extremely long access times with QODBC, and there seems to be no consistency to which SQL statement will fail each time. The entire process works fine using the same data if I move the Access database and the Quickbooks company file onto a client machine and do everything locally.
Additionally, what might be a related issue is that regardless of the settings for QODBC and whether I've specified allowing QODBC to open Quickbooks without the UI (in both the setup screen and the initial connection inside Quickbooks), QODBC refuses to open a connection to Quickbooks without first launching a copy of the Quickbooks UI (and will fail during a "Test Connection" if Quickbooks is not already running). This problem also goes away if I move everything onto a single PC.
The funny thing is, up until a couple of days ago, this whole process was working flawlessly ON the network. The client I'm designing this for swears they haven't changed anything on their network recently.
What am I overlooking?
-Mike |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-02 11:55:09 |
QODBC is designed to run on a workstation that has QuickBooks on it along with your application, which in this case is MS Access. It makes no difference if the companyfilename.qbw or the accessfilename.mdb is on a local drive (C:\) or a mapped network drive (f:, p: etc). The only QODBC difference, other than setup, is the response speed because calling data over a network is subject to network loading and server resources.
But MS Access on the other hand will choke when accessed by multiple users. Try running some queries using VB Demo to see what the true QODBC response is over the network with the QuickBooks company file on the server and try using only 1 network Acces user.
Auto-login is most likely not working because you changed the location of the company file but forgot to update the location in the QODBC General setup tab.
SP_LASTINSERTID doesn't work when using MS Access because when you use linked tables, MS Access will often start another connection to do a process, pass-through or append query. |
|
|
|
|
|
| Posted : 2006-11-02 15:34:50 |
| First I wanted to thank you for your prompt response. Let me elaborate a little more on the issue, especially in response to your suggestions.
Suggestion: QODBC is designed to run on a workstation that has QuickBooks on it along with your application, which in this case is MS Access. It makes no difference if the companyfilename.qbw or the accessfilename.mdb is on a local drive (C:\) or a mapped network drive (f:, p: etc). The only QODBC difference, other than setup, is the response speed because calling data over a network is subject to network loading and server resources.
- Network load seems to be at a minimum, the server (for the most part) seems to be stable. There was an issue with the Windows help services (helpsvc.exe) tying up 99% of the system resources once near the time the problems began, but after reboot, the problems persisted.
Suggestion: But MS Access on the other hand will choke when accessed by multiple users. Try running some queries using VB Demo to see what the true QODBC response is over the network with the QuickBooks company file on the server and try using only 1 network Acces user.
- I'm afraid I gave the wrong impression about the number of users. At most, we're talking 4 or 5 users within Access, and each will require an open connection to QuickBooks as well (based on that QODBC issue, as well as requiring it open for other accounting purposes). So that's a total of 10 users accessing the QuickBooks file, in a worst-case scenario. Additionally, I've tried using VB Demo to simulate the data reads I'm doing. It works as I would imagine is expected, with a delay of 10 to 20 seconds per table, depending on the size of the table. This is about the same as the single-machine test environment I'm developing in. Are inserts possible to simulate using the VB Demo? I couldn't get that to work without throwing an error. Finally, development has been done on a single client machine with only one network Access user, although I'm at the point where I should probably enlist the aid of fellow employees using our own network in order to simulate the target environment.
Suggestion: Auto-login is most likely not working because you changed the location of the company file but forgot to update the location in the QODBC General setup tab.
- This is definitely not the case. I've made doubly sure to set up QODBC accurately each time I try something new, as it's the point of contention here. The client I'm working for is also testing this process, and has become knowledgeable in your product. He's also been setting up your product accurately as well. We've done several re-installs from scratch, as well as changing between files with ease.
Suggestion: SP_LASTINSERTID doesn't work when using MS Access because when you use linked tables, MS Access will often start another connection to do a process, pass-through or append query.
- Is there a work-around? This stored procedure has been working for a while now (for the most part), and only when these issues cropped up has it become an issue. Using Access, I've been able to successfully use SP_LASTINSERTID for weeks.
I'm sorry to persist, but this is a critical issue. The client is upset, my employer is upset, and I'm stuck in the middle.
-Mike |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-02 16:16:59 |
|
|
|
|
|
| Posted : 2006-11-04 10:19:12 |
I thought it was a network issue, until I was able to reproduce it all locally. After spending time swapping out what was in production with what was in test, I've narrowed it down to differences in the company file. What is truly strange is that the errors don't seem to be consistent. The one that occurs most often now is that QODBC hangs while trying to do the insert into the Invoice table. The driver will say "Waiting On Quickbooks". I've waited as long as 45 minutes hoping it would resolve, but never did. However as I'm typing this, after just 5 minutes of waiting while doing the same thing, the one that wasn't working actually started to "Find Next Record". This process is now going through 100 records at a time, and I'm assuming there are close to a hundred thousand records (or more) in there. It might take hours. (FYI, I have QODBC set up to Optimize on every query).
Additionally, I was thinking about what you stated Tom, about how Access uses separate connections for append queries. Is it possible QODBC is returning control to Access too soon during these inserts, so that Access is actually trying to insert an Invoice before it's finished with all the InvoiceLine inserts?
Finally, I've attached the following as a partial print of the QODBC Log, which was the most revealing so far, but I'm still not sure exactly what's happening. At the end, it gave the typical error for creating an invoice, since it didn't insert the invoiceline items correctly.
2006-11-03 17:48:42 QODBC Ver: 6.00.00.176
****************************************************
IsAService: Unknown
LOADXML Problem
Parsing Error: 0 - ”¯í
2006-11-03 17:48:42 QODBC Ver: 6.00.00.176
*****************************************************
IsAService: False
SQL Statement: INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID","InvoiceLineDesc","InvoiceLineQuantity","InvoiceLineRate","InvoiceLineAmount",
"InvoiceLineSalesTaxCodeRefListID","FQSaveToCache") VALUES (?,?,?,?,?,?,?)
Error Creating Header XML on this table in BuildXMLHeader
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceAddRq requestID="1">
<InvoiceAdd defMacro="TxnID:57FEB5C2-6585-4365-828A-04477457A8BE">
</InvoiceAdd>
</InvoiceAddRq>
</QBXMLMsgsRq>
</QBXML>
2006-11-03 17:48:43 QODBC Ver: 6.00.00.176
***************************************************
IsAService: Unknown
LOADXML Problem
Parsing Error: 0 - Dlv
2006-11-03 17:48:43 QODBC Ver: 6.00.00.176
***************************************************
IsAService: False
SQL Statement: INSERT INTO "InvoiceLine" ("InvoiceLineDesc","FQSaveToCache") VALUES (?,?)
Error Creating Header XML on this table in BuildXMLHeader
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceAddRq requestID="1">
<InvoiceAdd defMacro="TxnID:C8B5FA7F-C8B1-4ECB-B536-E5465313F545">
</InvoiceAdd>
</InvoiceAddRq>
</QBXMLMsgsRq>
</QBXML>
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-04 11:09:42 |
I need to know two things:
a) What version of QuickBooks you are using? Edtion, Year and Country?
b) What happens if you do the insert using VB Demo? |
|
|
|
|
|
| Posted : 2006-11-07 05:00:21 |
Sorry for the delay in response, and once again thank you for the help. First, let me answer your questions.
a) I'm using Quickbooks Enterprise Edition Release R8P, 2006 V6 USA, however the company file was recently updated from QuickBooks 2005.
b) If I do the inserts using VB Demo, aside from having to change the date formatting, everything works beautifully. Additionally, I've had to remove carriage returns in the invoice line description fields since VB Demo can't handle that. Aside from those changes, these are direct cut-and-paste from debug.prints in Access.
I've noticed that one of three scenarios will occur most often when I try my inserts using Access using the same data each time. Usually the first one will occur only on the first try, and after that it is usually either item 2 or 3 below.
- The QODBC will encounter the error I've already shown the log for above, apparently being unable to insert the invoice line items. The error is returned almost immediately, with only a very minor delay, and all tables seem to optimize successfully except the InvoiceLine and Invoice tables.
- The line items seem to get entered successfully, but the invoice table gets "stuck" on optimizing the Invoice table, and just hangs while "Waiting on Quickbooks". I have waited on this for as long as 30 minutes, with no results.
- After a couple of minutes of "Waiting for Quickbooks" to start optimizing the Invoice table, it finally begins optimizing. At the rate it was going, I estimate this process will take around 4 or 5 hours, perhaps longer. I've never let it run fully, and have killed the service each time after around 15 minutes, and then rebooted.
During 2 & 3 above, when QODBC appears to be "Waiting on Quickbooks" I've noticed that in the running services the QBDBMgrN.exe file takes up about half the CPU resources, but appears to be doing nothing. Also, it seems to be loading the entire company file into memory, all 350MB of it. Should I be concerned that the file size might be the problem?
Finally, I just wanted to reiterate again that this was all working up until just recently. The only known differences between the working company file that had been in the development environment and the current non-working version that was in production are that the client has entered many new items (around 2000 more items) in QuickBooks and of course new invoices from a month of usage.
Any suggestions would be appreciated. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-07 10:38:00 |
| Ok, as the problem is on your clients site. Get a copy of their company file and run test operations in your development enviroment instead. You may need to rebuild their company file, from QuickBooks use File | Utilities | Rebuild Data. |
|
|
|
|
|
| Posted : 2006-11-07 11:18:39 |
I've been doing my recent testing in my development environment, and all of my recent findings were based on that. It definitely seems to be an issue with the company file, either it's size, perhaps something's gotten corrupted (although it has no trouble other than when using QODBC). The client recently had to rebuild the file, but I haven't checked for new errors since then (a few days have passed).
A few questions:
- Is the file size significantly important (350 MB)
- Based on what I've read elsewhere in the forums, I tried stopping the optimizer by setting "Use Optimizer" in the driver setup to unchecked. However it then just hangs on the "Waiting for Quickbooks" message. Should I just let that run overnight to see if it eventually completes?
- Should I turn the optimizer back on, and just let the optimizer run overnight? I think that when it gets that far, it means that the inserts have succeeded, and is now trying to run the SP_LASTINSERTID workaround you suggested previously in this thread. But if it does work, how often will it try to do this optimization? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-07 14:53:41 |
I've never seen a "Waiting for QuickBooks" message or heard of it from any other QODBC user in three years of doing support. Size is an issue, but I've worked with users with over 2GB company files. The key to using QODBC is to define what records you want and that you don't want 100,000 records returned in a set! As this topic was about doing INSERTs I haven't focused on query sets or size. So grab the original 2005 company file, convert it and try using it instead. Your issue seems to be with company file your using, not QODBC.
As far as the optimizer is concerned, our default setting of "The start of every query" is the best. You can also run a full optimization overnight first by using: QODBCFUL.EXE and passing a DSN on the command line.
|
|
|
|
|