I am attempting to speed up a couple of queries i have pulling from QB using the TimeModified as a 'jump-in', but am having trouble getting anything to work. For some reason i keep getting the same error "Expected lexical element not found" nomatter what i attempt to do. Ideally what i need is something like this "SELECT * FROM SalesReceiptLine WHERE TimeModified >= DateAdd(Day, -7, GetDate())". In attempting to do this i figured that my problem lies in the DateAdd Function... but i have tried all of the available examples that i can find for a timestamp, and i get the same error. (eg. even with this i get the same error: "SELECT * FROM SalesReceiptLine WHERE TimeModified > {ts 2007-11-15 00:00:00:001}". I am not understanding how the syntax can be incorrect when copying directly from the examples given. Again, my goal is the continually pull data from only 7 days ago, and i don't want to have to hand change my query every day. Is there a way to do this? If not please at least tell me what is wrong with my syntax.

BTW... i am using QB Enterprise solutions Retail Edition 7.0, and version 8.00.00.234 of the QODBC.

Thank you for your time

Ben

Well you are missing the quotes and the last delimiter is . (full stop) not a semi-colon : like this:

SELECT * FROM SalesReceiptLine WHERE TimeModified > {ts '2007-11-15 00:00:00.001'}

To automatically start from seven days ago based on the current date, do:

SELECT * FROM SalesReceiptLine WHERE TimeModified > {fn TIMESTAMPADD(SQL_TSI_YEAR, 0, ({fn CURDATE()})-7)}

The new syntax worked... however it does not seem to be working as i'd hoped. What flags the TimeModified field to be updated in QB?

I am attempting to pull quantity on hand on apprx. 13,000 items and update a db with the current quantity. I only need to pull items that the quantity is different then what i am showing in the db. I was trying to accomplish this by simply transferring the timemodified to the table in my db, and then when i pull the new info and update a temp table in the db, if the TimeModified in TEMP> TimeModified in Live then update the qty. This does not seem to work, as we have had problems with my query coming out of qb is not including items that we have just changed qty on. Is there another way to accomplish this, and be able to run this query from QB in less than 15mins?

If you could enlighten me on what flags the TimeModified field in the ItemInventory table, i could continue on from there

Thank you in advance

Ben

If you look at: How do I setup the QODBC Optimizer? Where are the Optimizer options? you will find my unofficial list of columns/fields affected by Null/Dirty Reads/Real Time as of March 27, 2007. All these columns are updated by QuickBooks without changing the TimeModified timestamp for the row. In database terms this breaks the biggest cardinal rule there is. When dealing with one of these columns you need to use the CALLDIRECT or UNOPTIMIZED tag to pull out,  as in your case the ItemInventory.QuantityOnHand, real time value:

SELECT QuantityOnHand FROM ItemInventory CALLDIRECT where FullName='Hardware:Lk Doorknobs'

But if you're only after the true Qty on Hand at any point of time, without optimizer and timemodified issues, then this will extract directly out of the QuickBooks engine in real time:

sp_report InventoryValuationDetail
show QuantityOnHand 
parameters ItemFilterFullNameWithChildren='Hardware:Lk Doorknobs'
where RowType='TotalRow'

A similar report can be used to extract all 13,000 items like this:

sp_report InventoryValuationSummary show Blank, ItemDesc, QuantityOnHand,
AverageCost, ValueOnHand, PercentOfTotalValue, UnitPrice, RetailValueOnHand,
PercentOfTotalRetail parameters DateMacro = 'ThisWeekToDate'
where {fn LOCATE('Total', "Blank", 1)} = 0 and Blank <> ''

Ok... new problem. I have been having trouble lately with some jobs i have scheduled to run nightly. They will run successfully for a couple nights, and then they will fail for 2-3nights, and if i just simply re-import the job into SQL (i'm using SQL 2005 and the QODBC Remote Connecter), then the job will work again for a couple nights. I am not sure what could be causing this, but we have also been having trouble with data problems in QB, this leading to having to re-build our data 2 times in the past 2 weeks... and also creating a Portable company file and re-installing it so that we have a fresh company file. I need to know who to talk to about the problem, so i would like to go to you first as you have a lot more "tech experience" then any of the tech support then i can get from QB. The table that seems to start it all is when i pull our SalesReceiptLine table... I am pulling 4 tables, PurchaseOrderLine, SalesReceiptLine, ItemInventory and ItemNonInventory. I had 3 of them working flawlessly for approx 3weeks until 2 days ago, when they started failing as well. As of now i can't get any of these jobs to run successfully. Interestingly enough, i have a job running every 2 hours during the day pulling ItemInventory.FullName and ItemInventory.QuantityOnHand, and this job works perfectly. As a last resort i attempted a test import from our SalesReceiptLineTable with this simple query: SELECT RefNumber FROM SalesReceiptLine... Failed with error: 1000 -There has been an internal error when processing the request.

Any help on where i should go from here would be greatly appreciated.

Thank you

Ben

I have to say that the QODBC Remote Connector and iBiz Remote Connector are only intended to be used with desktop applications. The QDOBC Server Edition and QODBC DCOMs should always be using with Windows Services or applications that run as a service such as MS SQL Server 2005. Having said that, QODBC also currently has a memory leak issue that I have reported on QSupport Ticket ID: BR00000069. Try rebooting your server daily and see if that helps.

Because you reading lots of records, you also need to increase your default iterator setting of 500 rows (if you haven't done so yet) to 10000 or 20000 rows.

Iterator or Chunk Size determines the size of the rowsets that are retrieved from QuickBooks. Set this value high if you are exporting records from QuickBooks. The maximum Iterator or Chunk size that can be set is 100000 (higher than that and it resets itself back to the default of 500).

Thank you for the quick reply,

So really to have everything run seemlessly we would need to purchase the Server Edition of QODBC instead of our currently purchased desktop edition?

Also, i have attempted before deciding to go with the remote connector to create a linked table for QB... and i couldn't ever get anywhere. Could this have to do with the edition of QODBC that i am running?

I will try rebooting our server as often as possible... Would you say that to have everything working correctly and be dependable that we need to purchase the server edition, or could we get by with the desktop edition?

Thanks

Ben