I've installed QODBC on my company's server. I created a linked server and can pass simple queries to it. My first issue is speed. A simple query like SELECT   *   FROM LINKED_QODBC...Customer  WHERE ListID = "some list id";  Takes 12-17 seconds to run. The queries I need to run are complex and won't work on the linked server.

So, I need to be able to mirror all of my QuickBooks tables on a separate database and update that data regularly.  I’ve searched the forums for a solution, but so far have come up empty. I need to create a SSIS import/export for all tables, but QODBC is not listed in the drivers’ list box. I unfortunately don’t have the time to write all the SQL to update these tables manually and I’m concerned about the speed at which these statements would execute, if I have no other choice but to write these statements myself.  If someone would be so kind as to point to a faster solution I would greatly appreciate it.

Thanks,

Chris

Try running:

SELECT * FROM OPENQUERY(LINKED_QODBC, 'SELECT ListID, Name FROM Customer WHERE ListID = "some list id"')

instead.

Ok, well try:

SELECT ListID, Name FROM Customer WHERE ListID = 'some list id'

using VB Demo driectly. See: How do I use the VB Demo for testing? for more.

CDitta, what solution did you come up with for keeping a QB database in sync with SQL 2005 ?

I am struggling with the same concepts ...  Tom, what is the best way to keep 2 way synchonization between a clone of the QB data in SL 2005 ?  

RIght now I have created a SSIS job referencing the linked server, and just trying to see what is the best approach.

Any suggestions greatly appreciated.

Select * from SalesOrderLine Calldirect where timemodified >= {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, {fn NOW()})}

Calldirect is used to grab the new and any modified rows from QuickBooks directly bypassing any optimizer updates.