I have DTS packages set up in SQL Server 2000 that successfully pull data into a few SQL tables via QODBC.  If I run the task from within the DTS package designer, it takes only a couple of minutes.  However, if I run the DTS package as a whole, it takes hours.  Now, the ONLY task I have in this DTS package is one query to pull data from one location, so it is not queueing up multiple requests. 

I have searched this board and found a reference to using the NOSYNC command in the query that pulls the data to SQL.  When I pop that in, the statement parses just fine.  When I actually try to run it, however, it fails with an "Unspecified Error".  Nothing gets logged in the QODBC logs as far as I can tell (by clicking the "Review QODBC Messages" button from within the QODBC configuration window in QuickBooks).  The version of QODBC that I have is 7.00.00.199.

Here is the statement that causes the error.  The same statement without the "NOSYNC" runs perfectly:

SELECT *

FROM CreditMemo NOSYNC

WHERE (CreditMemo.TxnDate>{‘2006-12-31’})

Thanks for any help!
Elliot

Sorry, you do have a unspecified syntax error, it should be:

SELECT * FROM CreditMemo NOSYNC
WHERE (CreditMemo.TxnDate>{d'2006-12-31'})

See also: Using DTS to Load QuickBooks Data into Microsoft SQL Server 

Darn.  I'm sorry, I had to manually copy the query when I wrote my post and I left out the "d". 

It is there, and the statement runs fine if I do not have "NOSYNC" in the query. 

I apologize for leaving out that important piece of the query.  Any other ideas?

Thanks,

Elliot

The real way to do this is to first optimize your tables by using the [ Load Updated Data ] or [ Reload All Data ] (if you have just started using QODBC) option in the QODBC setup screen.

Once optimization is completed (the time this takes is dependent on the size of your company file), use queries, not copy tables, with the NOSYNC command in the DTS Import/Export Wizard:

like this for each of the tables you wish to import:

select * from CreditMemo NOSYNC

This problem most likely is caused by SQL_SLONG types. In QODBC v7.00.00.204 we added conversion support for SQL_C_SLONG, SQL_C_ULONG, and SQL_C_SSHORT (see: QODBC v7.0.0.204 Released ) 

Please update to QODBC v7.00.00.207, see: How can I get the latest version of QODBC? 

Thanks for this.  I updated the driver, and the query now works with NOSYNC, but that has not solved the problem of the DTS package taking an inordinate amount of time to run (whereas it runs quickly if I run the query from within the package).

Is there any other configuration setting somewhere that I can check?  I have already modified the Chunk Size, and using NOSYNC was the only other tip I have been able to find on solving this problem. 

Elliot

OK, so if I include the NOSYNC statement in the query, it runs quickly.  I thnk the problem I was having with that was that I was trying it via Remote Desktop.

So I am now doing the testing directly on the server itself.  So, when I have NOSYNC in the query,  it only returns data up to January 8.  When I ran the Optimization initially, I did it via a Remote Desktop connection to the server.  Could that be causing the problem here?  I am wondering because if I have NOSYNC in the query and I preview it via Remote Desktop, all the data is there.  Do I need to re-run the optimization directly on the server rather than throguh Remote Desktop?

Thanks,

Elliot