Hello,

I read on this forum that I can resynch optimized tables by issuing a command like this:
sp_optimizefullsync SalesOrderLine

I need to resynch the SalesOrderLine and InvoiceLine tables each hour then, import the changed data into SQL Server.
I understand how to import the data into SQL Server using QODBC and SQL Server Integration Services.
The thing I need help with is, how can i run scheduled resynchs like the one above to occur every two hours?

Do I use task scheduler and just type in some command line syntax?
And if so, what would the command line syntax look like?

Thanks,
Brian

You don't need to schedule resynchs, because by default the QODBC optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the local optimized table. This is faster than reading everything out of QuickBooks every time or resynching a table, especially the more data you have.

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more information.

Tom,

Thanks for the fast reply.
I just don't get it though....

I run a query through SQL Server, using the QODBC driver, this is the query:

SELECT
SalesOrderLine.TxnID,
SalesOrderLine.CustomerRefFullName,
SalesOrderLine.TxnDate,
SalesOrderLine.RefNumber,
SalesOrderLine.PONumber,
SalesOrderLine.DueDate,
SalesOrderLine.ShipDate,
SalesOrderLine.ShipMethodRefFullName,
SalesOrderLine.SalesOrderLineItemRefFullName,
SalesOrderLine.SalesOrderLineQuantity,
SalesOrderLine.SalesOrderLineRate,
SalesOrderLine.SalesOrderLineAmount,
FROM SalesOrderLine

As soon as I run the query, I see a window popup saying QODBC Optimizer.
And the query runs for like 10 minutes, before I cancel it, because that is just too long for it to run.

Then, I tried this using the VB Test program:

sp_optimizefullsync SalesOrderLine

That took about 30 seconds to run.

Then I ran the same query with a NOSYC added after the table name (again using the VB Test program) , and the results came in under 30 seconds. Then I ran the same query with the NOSYC in SQL and the results again were returned quickly (under one minute.)

So, the thing I don't get is that you say I don't need to run the sp_optimizefullsync SalesOrderLine

BUT when I don't run that first, the query runs for well over ten minutes when I execute it through the QODBC driver from SQL Server without the NOSYNC directive (which I think I need to leave out in order to let the optimizer do it's thing.)

Can you shed some help on what I may be doing wrong?

We are using SQL Server 2005 and are very interested in purchasing QODBC, but will be unable to pruchase it unless we are able to get these queries to run in two minutes or less.

One thing to not, even when I query for data with a TxnDate within the last couple days, not using NOSYNC, the query runs for over five minutes.

PLEASE HELP!

Thanks,
Brian

You can do what the QODBC optimizer does by default by executing the following two SQL Server 2005 LINKED table queries instead:

SELECT * FROM OPENQUERY(QODBC, 'sp_optimizeupdatesync SalesOrderLine')

SELECT * FROM OPENQUERY(QODBC, 'SELECT SalesOrderLine.TxnID, SalesOrderLine.CustomerRefFullName, SalesOrderLine.TxnDate, SalesOrderLine.RefNumber, SalesOrderLine.PONumber, SalesOrderLine.DueDate, SalesOrderLine.ShipDate, SalesOrderLine.ShipMethodRefFullName, SalesOrderLine.SalesOrderLineItemRefFullName, SalesOrderLine.SalesOrderLineQuantity, SalesOrderLine.SalesOrderLineRate, SalesOrderLine.SalesOrderLineAmount FROM SalesOrderLine NOSYNC')

See: Does QODBC work with MS SQL Server 2005 Linked Tables? for more.

Tom,

I tried to follow the directions for creating a Linked Server in SQL Server 2005 here:
http://www.qdeveloper.com.au/forum.php?homeinclude=topicdetails&forum_id=1&category_id=2&post_id=549

All seemed to work well, except I did not get the properties page, you showed.

Anyhow, I am able to execute SELECT queries just fine, but when I try to execute this stored procedure:
SELECT * FROM OPENQUERY(QODBC, 'sp_optimizefullsync SalesOrderLine')

After 8 seconds I get this error message:
Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

Any ideas?

Tom,

I am not sure exactly what i should be writing for my query (as a workaround.)

The linked server name I created is QODBC, but do you know what catalog, schema and object-name I want to call?

Can you give me an example of the exact SQL syntax you think I might need to write?

Something like this?
SELECT * FROM OPENQUERY(QODBC.???.???.???, 'sp_optimizefullsync SalesOrderLine')

Thanks,
Brian

Tom,

I think I figured out the four-part name thing, but it does not appear to make a difference.
Here is what I tried:

QODBC...sp_optimizeupdatesync SalesOrderLine

It executes but returns the same error as before.

Maybe if I tell you exactly what we are trying to do, you can help with advice on how you would recommend to do it.

We have a SQL Server database that we use to show customers account information. We want to use QODBC to pull data from the SalesOrderLine and InvoiceLine tables into SQL Server every 15 minutes, so that customers can log into our website and see (near) current information on sales orders and invoices.

We created an SSPI package that queries through QODBC to pull in all of the SalesOrderLine (586 records) and InvoiceLine (1200 records) data from Quickbooks into SQL Server. The problem is that this package takes 15 minutes to run. And this is only for six months data. I am guessing that it will just take longer and longer as we get more data.

One thing I did try to do, is just query for the last week's data (returning a smaller result set) but for some reason the WHERE clause just seems to add to the time it takes to execute the package instead of shortening the time. Am I doing something wrong?

Can you recommend anything for us to do to shorten this update time?

Thanks,
Brian Philpot

Ok, what should be a simple task isn't because QuickBooks doesn't always change the modified timestamp when for example a invoice is paid etc. That's why the QODBC optimizer has to look at the ReceivePaymment table to optimize the InvoiceLIne table. So providing you just want to do simple updates every 15 mintues you can actually do:

SELECT * FROM OPENQUERY(QODBC, 'SELECT SalesOrderLine.TxnID, SalesOrderLine.CustomerRefFullName, SalesOrderLine.TxnDate, SalesOrderLine.RefNumber, SalesOrderLine.PONumber, SalesOrderLine.DueDate, SalesOrderLine.ShipDate, SalesOrderLine.ShipMethodRefFullName, SalesOrderLine.SalesOrderLineItemRefFullName, SalesOrderLine.SalesOrderLineQuantity, SalesOrderLine.SalesOrderLineRate, SalesOrderLine.SalesOrderLineAmount FROM SalesOrderLine where TimeModified > {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, {fn NOW()})}')

which will automatically retreive only the records in the SalesOrderLine table that have been changed in the last 15 minutes.

I have also put in an enhancement request to have 'sp_optimizeupdatesync SalesOrderLine' return values so MS SQL Server can use the stored procedure in the future.