No data returned using optimizer with QuickBooks 2008 Canadian Edition |
Author |
Message |
|
Posted : 2008-04-29 04:43:58 |
Hi,
I've been trying to configure QODBC for several days now with limited success.
I have followed the instructions in every thread that seemed relevant, and I am still unable to access data while the optimizer is enabled.
I've already read How can I see QuickBooks data in a Internet Explorer Browser using Windows XP?, How do I do INSERTs, UPDATEs and DELETEs using OPENQUERY with Linked MS SQL Servers?, and Does QODBC work with MS SQL Server 2005 Linked Tables?.
When I run the DCOM test script in SQL Server, all 5 tests pass.
If the optimizer is turned off, this query returns results: SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Account ')
When the optimizer is enabled, and there is an optimizer file, the same query returns headings but no rows.
Running this query SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Account Unoptimized') while the optimizer is enabled returns the rows.
When I try to run this query: SELECT * FROM OPENQUERY(QODBC,'sp_optimizefullsync Account') I get this error:
Msg 7357, Level 16, State 2, Line 7 Cannot process the object "sp_optimizefullsync Account". The OLE DB provider "MSDASQL" for linked server "QODBC" indicates that either the object has no columns or the current user does not have permissions on that object.
If I turn the optimizer on, then set it to optimize "the last time I pressed one of the load data buttons", I can access data using SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Customer ') for a while. After a few minutes, data stops being returned.
The VB Demo is able to access data regardless of the optimizer setting.
The SDK Messages when retrieving data doesn't work looks like:
20080428.142908 I 5992 RequestProcessor ========= Started Connection ========= 20080428.142908 I 5992 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080428.142908 I 5992 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080428.142908 I 5992 CertVerifier The Authenticode signature validated OK. 20080428.142910 I 5992 RequestProcessor Opening the file in the DoNotCare mode. 20080428.142914 I 5064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5992). 20080428.142914 I 5064 SpecVersion Current version of qbXML in use: 6.0 20080428.142914 I 5064 QBSDKMsgSetHandler QUERY: Host 20080428.142914 I 5064 QBSDKMsgSetHandler Request 1 completed successfully. 20080428.142914 I 5064 QBSDKMsgSetHandler QUERY: Company 20080428.142914 I 5064 QBSDKMsgSetHandler Request 2 completed successfully. 20080428.142914 I 5064 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080428.142914 I 5064 QBSDKMsgSetHandler Request 3 completed successfully. 20080428.142914 I 5064 MsgSetHandler Finished. 20080428.142914 I 5064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5992), ret = 0. 20080428.142915 I 5064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5992). 20080428.142915 I 5064 SpecVersion Current version of qbXML in use: 6.0 20080428.142915 I 5064 QBSDKMsgSetHandler QUERY: Customer Job 20080428.142915 I 5064 CustomerStorage::DoQuery Returning no more than 00000500 objects 20080428.142915 I 5064 QBSDKMsgSetHandler Request 1 completed successfully. 20080428.142915 I 5064 MsgSetHandler Finished. 20080428.142915 I 5064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5992), ret = 0. 20080428.143018 I 5992 RequestProcessor "EndSession" has not been called. 20080428.143019 I 5992 RequestProcessor "CloseConnection" has not been called.
And when it is working (through the VB Demo) it looks like this:
20080428.143021 I 5992 RequestProcessor ========= Started Connection ========= 20080428.143021 I 5992 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080428.143021 I 5992 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080428.143021 I 5992 CertVerifier The Authenticode signature validated OK. 20080428.143023 I 5992 RequestProcessor Opening the file in the DoNotCare mode. 20080428.143027 I 4480 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5992). 20080428.143027 I 4480 SpecVersion Current version of qbXML in use: 6.0 20080428.143027 I 4480 QBSDKMsgSetHandler QUERY: Host 20080428.143027 I 4480 QBSDKMsgSetHandler Request 1 completed successfully. 20080428.143027 I 4480 QBSDKMsgSetHandler QUERY: Company 20080428.143027 I 4480 QBSDKMsgSetHandler Request 2 completed successfully. 20080428.143027 I 4480 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080428.143027 I 4480 QBSDKMsgSetHandler Request 3 completed successfully. 20080428.143027 I 4480 MsgSetHandler Finished. 20080428.143027 I 4480 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5992), ret = 0. 20080428.143030 I 4480 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5992). 20080428.143030 I 4480 SpecVersion Current version of qbXML in use: 6.0 20080428.143030 I 4480 QBSDKMsgSetHandler QUERY: Customer Job 20080428.143030 I 4480 CustomerStorage::DoQuery Returning no more than 00000500 objects 20080428.143030 I 4480 QBSDKMsgSetHandler Request 1 completed successfully. 20080428.143030 I 4480 MsgSetHandler Finished. 20080428.143030 I 4480 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5992), ret = 0. 20080428.143037 I 5992 RequestProcessor Connection closed by app named 'FLEXquarters QODBC' 20080428.143037 I 5992 RequestProcessor ========== Ended Connection ==========
The only difference I see is:
20080428.143018 I 5992 RequestProcessor "EndSession" has not been called. 20080428.143019 I 5992 RequestProcessor "CloseConnection" has not been called.
vs.
20080428.143037 I 5992 RequestProcessor Connection closed by app named 'FLEXquarters QODBC'
I have a trace log, but it's 100s of pages long.
Please let me know other steps I can take to get the optimized quickbooks data to appear as a linked server.
Thanks,
Aaron . |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-29 08:09:28 |
|
|
|
|
Posted : 2008-04-29 22:20:48 |
Hi Tom,
I've already attempted the soltuion in MS SQL Server 2000 Linked Server issue using the QODBC optimizer to no avail.
When I try to run this query: SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Invoice VERIFY WHERE TxnID = "x"')
I get the following error:
Msg 7399, Level 16, State 1, Line 7 The OLE DB provider "MSDASQL" for linked server "QODBC" reported an error. The provider did not give any information about the error. Msg 7321, Level 16, State 2, Line 7 An error occurred while preparing the query "SELECT * FROM Invoice VERIFY WHERE TxnID = "x"" for execution against OLE DB provider "MSDASQL" for linked server "QODBC".
I rebuilt the entire 118 meg .OPT file last night, and tried the query again this morning. Same thing.
The way I rebuild the OPT file is as follows:
1) Disable Optimizer, drop linked table, wait 5 minutes. 2) Delete OPT file. 3) Open data sources from control panel. Open system DSN. Turn optimizer back on and click "Reload All Data". Let it run overnight.
When I came in this morning, I copied the OPT file (so I now have [name].OPT and 'copy of [name].opt' in the same folder). I plan to copy and rename this copy from now on, rather than rebuilding.
The first thing I did after copying the optimizer file is run a query. No results. Ran it unoptimized, got results.
I tried the first query in the VB demo and the optimizer worked / returned data quickly. I tried it on the invoices table as well.
I am running Windows Server 2003 SBS edition with Canadian QuickBooks 2008 Premier (non-multi currency edition) installed.
I am trying to use QODBC as a linked server in Management Studio Express for an SQL Express 2005 database.
I am attaching pictures of all the settings. Any additional guidance would be much appreciated.
Thanks,
Aaron |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-30 08:15:29 |
Delete or rename the opt file used by your linked server and then try running:
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Customer VERIFY')
|
|
|
|
|
Posted : 2008-04-30 08:20:31 |
I got headings and no results again.
First run took considerably longer than the second.
There is now a 204 KB OPT file |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-30 08:24:28 |
Do:
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Customer VERIFY')
and wait for the whole query to be returned before doing:
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Customer')
or:
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Customer CALLDIRECT') |
|
|
|
|
Posted : 2008-04-30 08:53:54 |
First I tried calling 4 queries. That didn't work.
Then I deleted the OPT file, ran the verify query, then ran the "SELECT * FROM Customr" query and got no results.
The verify query took 17 seconds, the other query took 6.
I ran the verify query again, then ran the calldirect query. It took 17 seconds for the verify query, and 8 seconds to return results.
"CALLDIRECT | UNOPTIMIZED - Passthru query directly to QuickBooks - use no optimizations" call direct and unoptimized do the same thing, bypass the optimizer, which is what seems to cause the problem.
Any idea why the verify query takes twice as long as a direct call, or why the optimizer wont return any rows to the sql server? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-30 09:22:06 |
Please turn off the trace log and make sure the verify query has TOTALLY completed before commencing another query (or query thread)! The optimizer is single user/thread only when building optimized tables. The VERIFY tag does a complete rebuild of the optimized table and then executes the query. |
|
|
|
|
Posted : 2008-04-30 09:45:12 |
I turned the trace log off and deleted the OPT file.
Then, I ran SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Customer VERIFY').
It took 16 seconds and returned results. This was with the optimizer turned off.
I ran the same query with the optimizer on, and got no results. I did get an OPT file. It took 16 seconds.
I ran it again. It took 10 seconds this time. Once again, it returned no results.
I ran it without the VERIFY. It look less than a second, but returned no results. I did it several more times. Still no results.
I then deleted the OPT file.
I called it with UNOPTIMIZED. It took 8 seconds and returned results.
I called it with CALLDIRECT. It took 8 seconds and returned results.
I then called it with VERIFY. It took 10 seconds and returned no results.
I called it without VERIFY. It returned no results instantly (still returning headers though).
I re-verified, then ran the query with CALLDIRECT again. It took 15 seconds and returned results (Why does call direct take wtice as long when there is an OPT file for the table?).
I ran the CALLDIRECT query again, and it only take 8 seconds.
What should I try next?
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-30 10:26:13 |
The VERIFY rebuild of the optimization table doesn't work correctly unless the optimizer is on. I would guess you are also corrupting your opt file, check the QODBC Message log in the Messages tab of the QODBC Setup Screen for optimizer errors. |
|
|
|
|
Posted : 2008-04-30 22:16:01 |
The QODBC Message log is empty.
The trace log was off.
I'm including a sample of the SDK messages.
Any idea what I can do to get this working? All I really need to do is dump the quickbooks data to a SQL server database, and insert transactions in to QuickBooks. If I can't use the optimizer to do this, I won't be able to update the database version on a daily basis (at least, not without writing stored procedures to only pull data that has been updated since the last read -- essentially creating a SQL server version of the optimizer.)
Are you aware of any workarounds for my situation?
Thanks for your assistance,
Aaron
20080429.195833 I 4992 RequestProcessor ========= Started Connection ========= 20080429.195833 I 4992 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080429.195833 I 4992 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080429.195833 I 4992 CertVerifier The Authenticode signature validated OK. 20080429.195836 I 4992 RequestProcessor Opening the file in the DoNotCare mode. 20080429.195840 I 2064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4992). 20080429.195840 I 2064 SpecVersion Current version of qbXML in use: 6.0 20080429.195840 I 2064 QBSDKMsgSetHandler QUERY: Host 20080429.195840 I 2064 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.195840 I 2064 QBSDKMsgSetHandler QUERY: Company 20080429.195840 I 2064 QBSDKMsgSetHandler Request 2 completed successfully. 20080429.195840 I 2064 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080429.195840 I 2064 QBSDKMsgSetHandler Request 3 completed successfully. 20080429.195840 I 2064 MsgSetHandler Finished. 20080429.195840 I 2064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4992), ret = 0. 20080429.195840 I 2064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4992). 20080429.195841 I 2064 SpecVersion Current version of qbXML in use: 6.0 20080429.195841 I 2064 QBSDKMsgSetHandler QUERY: Customer Job 20080429.195841 I 2064 CustomerStorage::DoQuery Returning no more than 00000500 objects 20080429.195841 I 2064 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.195841 I 2064 MsgSetHandler Finished. 20080429.195841 I 2064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4992), ret = 0. 20080429.200025 I 2064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4992). 20080429.200025 E 2064 TagDefs Lookup: looking for CurrencyRef, but not found! 20080429.200025 W 2064 QBNCustomerQueryRq::AddIncludeRetElement IncludeRetElement specified unknown tag 'CurrencyRef'. 20080429.200025 E 2064 TagDefs Lookup: looking for IsUsingCustomerTaxCode, but not found! 20080429.200025 W 2064 QBNCustomerQueryRq::AddIncludeRetElement IncludeRetElement specified unknown tag 'IsUsingCustomerTaxCode'. 20080429.200025 I 2064 SpecVersion Current version of qbXML in use: 6.0 20080429.200025 I 2064 QBSDKMsgSetHandler QUERY: Customer Job 20080429.200025 I 2064 CustomerStorage::DoQuery Returning no more than 00000500 objects 20080429.200028 I 2064 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.200028 I 2064 MsgSetHandler Finished. 20080429.200028 I 2064 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4992), ret = 0. 20080429.200140 I 4992 RequestProcessor "EndSession" has not been called. 20080429.200141 I 4992 RequestProcessor "CloseConnection" has not been called.
20080429.200158 I 6488 RequestProcessor ========= Started Connection ========= 20080429.200158 I 6488 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080429.200158 I 6488 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080429.200158 I 6488 CertVerifier The Authenticode signature validated OK. 20080429.200200 I 6488 RequestProcessor Opening the file in the DoNotCare mode. 20080429.200204 I 6208 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 6488). 20080429.200204 I 6208 SpecVersion Current version of qbXML in use: 6.0 20080429.200204 I 6208 QBSDKMsgSetHandler QUERY: Host 20080429.200204 I 6208 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.200204 I 6208 QBSDKMsgSetHandler QUERY: Company 20080429.200204 I 6208 QBSDKMsgSetHandler Request 2 completed successfully. 20080429.200204 I 6208 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080429.200204 I 6208 QBSDKMsgSetHandler Request 3 completed successfully. 20080429.200204 I 6208 MsgSetHandler Finished. 20080429.200204 I 6208 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 6488), ret = 0. 20080429.200205 I 6208 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 6488). 20080429.200205 I 6208 SpecVersion Current version of qbXML in use: 6.0 20080429.200205 I 6208 QBSDKMsgSetHandler QUERY: Customer Job 20080429.200205 I 6208 CustomerStorage::DoQuery Returning no more than 00000500 objects 20080429.200205 I 6208 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.200205 I 6208 MsgSetHandler Finished. 20080429.200205 I 6208 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 6488), ret = 0. 20080429.200311 I 6488 RequestProcessor "EndSession" has not been called. 20080429.200312 I 6488 RequestProcessor "CloseConnection" has not been called.
20080429.200826 I 7172 RequestProcessor ========= Started Connection ========= 20080429.200826 I 7172 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080429.200826 I 7172 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080429.200826 I 7172 CertVerifier The Authenticode signature validated OK. 20080429.200828 I 7172 RequestProcessor Opening the file in the DoNotCare mode. 20080429.200832 I 3680 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 7172). 20080429.200832 I 3680 SpecVersion Current version of qbXML in use: 6.0 20080429.200832 I 3680 QBSDKMsgSetHandler QUERY: Host 20080429.200832 I 3680 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.200832 I 3680 QBSDKMsgSetHandler QUERY: Company 20080429.200832 I 3680 QBSDKMsgSetHandler Request 2 completed successfully. 20080429.200832 I 3680 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080429.200832 I 3680 QBSDKMsgSetHandler Request 3 completed successfully. 20080429.200832 I 3680 MsgSetHandler Finished. 20080429.200832 I 3680 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 7172), ret = 0. 20080429.200832 I 3680 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 7172). 20080429.200833 I 3680 SpecVersion Current version of qbXML in use: 6.0 20080429.200833 I 3680 QBSDKMsgSetHandler QUERY: Customer Job 20080429.200833 I 3680 CustomerStorage::DoQuery Returning no more than 00000500 objects 20080429.200833 I 3680 QBSDKMsgSetHandler Request 1 completed successfully. 20080429.200833 I 3680 MsgSetHandler Finished. 20080429.200833 I 3680 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 7172), ret = 0. 20080429.201012 I 7172 RequestProcessor "EndSession" has not been called. 20080429.201013 I 7172 RequestProcessor "CloseConnection" has not been called. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-01 07:42:30 |
By the looks of things the CurrencyRef and IsUsingCustomerTaxCode errors means that the optimized table for CUSTOMER isn't being built correctly. Please confirm exactly what version of QuickBooks you are using? By this I mean country, edition type and update revision (do Ctrl-1 while QuickBooks is running to see what your latest update is). |
|
|
|
|
Posted : 2008-05-01 09:50:46 |
Canadian version
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-01 10:31:16 |
Please confirm that you are your running QuickBooks 2008 Canadian Premier Contractor Edition? |
|
|
|
|
Posted : 2008-05-01 13:03:47 |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-01 14:28:18 |
I have requested the QODBC Software Engineer to review your errors under QSupport Ticket ID: BR00000085. I don't have a time frame on when I will receive a response. In the meantime you will need to use the customer table unoptimized because if there is an internal error optimizing a table the optimized table is deleted rather than displaying corrupted data. |
|
|
|
|
Posted : 2008-05-02 09:26:18 |
I ran some tests on the HOST table, which has no errors. The optimizer still doesn't return results.
SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM HOST VERIFY')
gives no results and SDK messages:
20080501.190636 I 1236 RequestProcessor ========= Started Connection ========= 20080501.190636 I 1236 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080501.190636 I 1236 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080501.190637 I 1236 CertVerifier The Authenticode signature validated OK. 20080501.190639 I 1236 RequestProcessor Opening the file in the DoNotCare mode. 20080501.190643 I 5132 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1236). 20080501.190643 I 5132 SpecVersion Current version of qbXML in use: 6.0 20080501.190643 I 5132 QBSDKMsgSetHandler QUERY: Host 20080501.190643 I 5132 QBSDKMsgSetHandler Request 1 completed successfully. 20080501.190643 I 5132 QBSDKMsgSetHandler QUERY: Company 20080501.190643 I 5132 QBSDKMsgSetHandler Request 2 completed successfully. 20080501.190643 I 5132 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080501.190643 I 5132 QBSDKMsgSetHandler Request 3 completed successfully. 20080501.190643 I 5132 MsgSetHandler Finished. 20080501.190643 I 5132 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1236), ret = 0. 20080501.190643 I 5132 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1236). 20080501.190643 I 5132 SpecVersion Current version of qbXML in use: 6.0 20080501.190643 I 5132 QBSDKMsgSetHandler QUERY: Host 20080501.190643 I 5132 QBSDKMsgSetHandler Request 1 completed successfully. 20080501.190643 I 5132 MsgSetHandler Finished. 20080501.190643 I 5132 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1236), ret = 0.
SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM HOST ')
gives no results and SDK messages:
20080501.190926 I 4180 RequestProcessor ========= Started Connection ========= 20080501.190926 I 4180 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080501.190926 I 4180 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080501.190926 I 4180 CertVerifier The Authenticode signature validated OK. 20080501.190929 I 4180 RequestProcessor Opening the file in the DoNotCare mode. 20080501.190933 I 5924 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4180). 20080501.190933 I 5924 SpecVersion Current version of qbXML in use: 6.0 20080501.190933 I 5924 QBSDKMsgSetHandler QUERY: Host 20080501.190933 I 5924 QBSDKMsgSetHandler Request 1 completed successfully. 20080501.190933 I 5924 QBSDKMsgSetHandler QUERY: Company 20080501.190933 I 5924 QBSDKMsgSetHandler Request 2 completed successfully. 20080501.190933 I 5924 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080501.190933 I 5924 QBSDKMsgSetHandler Request 3 completed successfully. 20080501.190933 I 5924 MsgSetHandler Finished. 20080501.190933 I 5924 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4180), ret = 0. 20080501.190933 I 5924 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4180). 20080501.190933 I 5924 SpecVersion Current version of qbXML in use: 6.0 20080501.190933 I 5924 QBSDKMsgSetHandler QUERY: Host 20080501.190933 I 5924 QBSDKMsgSetHandler Request 1 completed successfully. 20080501.190933 I 5924 MsgSetHandler Finished. 20080501.190933 I 5924 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4180), ret = 0. 20080501.191034 I 4180 RequestProcessor "EndSession" has not been called. 20080501.191035 I 4180 RequestProcessor "CloseConnection" has not been called.
and
SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM HOST UNOPTIMIZED')
has a result and gives SDK messages:
20080501.191232 I 5172 RequestProcessor ========= Started Connection ========= 20080501.191232 I 5172 RequestProcessor Request Processor, QBXMLRP2 v7.0 20080501.191232 I 5172 RequestProcessor Connection opened by app named 'FLEXquarters QODBC' 20080501.191232 I 5172 CertVerifier The Authenticode signature validated OK. 20080501.191235 I 5172 RequestProcessor Opening the file in the DoNotCare mode. 20080501.191238 I 6072 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5172). 20080501.191239 I 6072 SpecVersion Current version of qbXML in use: 6.0 20080501.191239 I 6072 QBSDKMsgSetHandler QUERY: Host 20080501.191239 I 6072 QBSDKMsgSetHandler Request 1 completed successfully. 20080501.191239 I 6072 QBSDKMsgSetHandler QUERY: Company 20080501.191239 I 6072 QBSDKMsgSetHandler Request 2 completed successfully. 20080501.191239 I 6072 QBSDKMsgSetHandler QUERY: DataExtension Definition 20080501.191239 I 6072 QBSDKMsgSetHandler Request 3 completed successfully. 20080501.191239 I 6072 MsgSetHandler Finished. 20080501.191239 I 6072 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5172), ret = 0. 20080501.191239 I 6072 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5172). 20080501.191239 I 6072 SpecVersion Current version of qbXML in use: 6.0 20080501.191239 I 6072 QBSDKMsgSetHandler QUERY: Host 20080501.191239 I 6072 QBSDKMsgSetHandler Request 1 completed successfully. 20080501.191239 I 6072 MsgSetHandler Finished. 20080501.191239 I 6072 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5172), ret = 0.
I got QODBC messages for a short period of time, then they stopped.
Several of these:
2008-05-01 19:01:54 QODBC Ver: 8.00.00.242 ********************************************************************************************************************* IsAService: False SQL Statement: select * from Customers Invalid Table Name in Open Table. (Customers)-2
and these
2008-05-01 19:02:25 QODBC Ver: 8.00.00.242 ********************************************************************************************************************* IsAService: False Error Code: 1 SQLOptimize_ExecuteNonQuery step: ATTACH_PASSPHRASE "SYSTEMC:\QODBC\xxx.OPT!CS;`n/-['<W5RnI%%W(W(WWfK0W" "C:\QODBC\Phoxxx.OPT" AS "FQBase"
and these
2008-05-01 19:02:25 QODBC Ver: 8.00.00.242 ********************************************************************************************************************* IsAService: False Error Code: -1 SQLOptimize_SQLOpen attach: C:\QODBC\xxxxxx.OPT
The last errors were from 2008-05-01 19:02:25 (before I ran the queries mentioned at the begining.) |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-03 11:18:35 |
|
|
|
|
Posted : 2008-06-02 22:45:40 |
Hi Tom,
I have everything more or less working now, but I have a question related to the new field mappings.
With the 2007 version of Canadian QuickBooks, I was able to retreive the GST and PST on an invoice using Tax1Total and Tax2Total.
Having upgraded to 2008 on my development server, I am no longer able to retreive these two fields. Now, I get SalesTaxPercentage and SalesTaxTotal.
I retrieved one of the transactions entered with the 2007 version through 2008 to understand the differences.
On an invoice with a sub total of $3,111.80 there was $186.71 GST and $59.58 PST.
The GST was at 6% and applied to the entire sub total. PST was 8% and applied to a single line item valued at $744.80.
SalesTaxPercentage had a value of 6 and SalesTaxTotal had a value of 246.29 (technically, this is wrong if you ask me).
With the 2008 version of Canadian QuickBooks, is it possible to retreive the GST and PST on an invoice seperately? Or do they have to be calculated using line items? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-03 07:57:10 |
Sorry, I haven't seen a version of QODBC with the correct new field mappings to see what's happening at this stage. |
|
|
|
|
Posted : 2008-06-03 08:05:56 |
No worries.
My application ignores taxes in every calculation (a job with 6% GST is no more or less profitable than one that had 6% GST and 8% PST), so I'll just create a "lumpSumTax" item to use in the mean time.
Can you please post a reply to this thread when updated mappings or additional information is available?
Thanks. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-10-06 16:29:45 |
|
|
|