I am using QODBC to pull data into SQL 2005 for use with SQL Server Reporting Services.  I have some reports that I wrote for my clients based on Class, PurchaseOrderLine and SalesOrderLine.

My current process is to import those tables into Access every evening, and then re-insert them up from the client's network to the web server that has both the SQL tables and the reporting services.  This works ok, but remains a manual process and 20-30 minutes worth of work.  I'd like to optimize this and automate it better.

My questions:

1) Using SQL Server Integration Services (SSIS), I do not have the option to talk to the QODBC driver directly.  Instead, the best option I have found is the .NET ODBC provider.  When I use that, I have to build the query manually as opposed to just selecting the table like I do if I'm importing into Access.  This is fine in theory, but the performance is about 1/10th the speed of access once it starts up.  My question here is, how can I pull information out of QODBC using SSIS with the same or similar speed as what Access is doing for me? 

2) I would LOVE to just grab the recordset updated since the last time the QB to SQL script is run.  But it doesn't appear to work the way I want it to.  For example, using:

select * from QODBC...SalesOrderLine nosync where SalesOrderLine.TxnDate >={d'2006-11-15}

brings up a driver status window that reads every single line in my SalesOrderLine table.  That table has around 15,000 rows and takes about 15 minutes in access.  It takes HOURS running as above using a linked table in SQL (how I test my SSIS scripts).

Let me know if I need to provide more information.  Been stuck on this one for months!

-Dale

What version of QuickBooks and QODBC are you using here? Try running:

select * from SalesOrderLine nosync where SalesOrderLine.TxnDate >={d'2006-11-15'} and TxnDate <={d'2008-11-15'}

in VB Demo and uncheck the Display Driver Status Panel option.

Quickbooks Manufacturing & Wholesale Edition Version 6.0
QODBC 6.0.0.176

I've made some progress - I can get the Class table (150 records) in about a second now.  The query you asked me to run in the VB Demo takes about 15 seconds to complete.  I think I had the optimizer set up wrong.

My problem now is when I run the following query, it begins a very slow process against InvoiceLinkedTxn:

select * from salesorder optimized

Why is it looking in InvoiceLinkedTxn?  Any way to speed this up.  Thanks for the help!

-Dale

We look at the InvoiceLinkedTxn table to check if any linked Sales Orders to Invoices have changed so we can update the optimized SalesOrderLine records. The solution is make sure the InvoiceLinkedTxn table is optimized before running:

select * from SalesOrderLine nosync where SalesOrderLine.TxnDate >={d'2006-11-15'} and TxnDate <={d'2008-11-15'}

Note: It's faster to have a from date and a to date.

Ok, that does get me closer, however the SalesOrderLine table is still giving me trouble.

1) I cannot run the following statement from SQL Server:

QODBC2...sp_optimizeupdatesync Class

It gives the following error:

Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'sp_optimizeupdatesync' on remote server 'QODBC2'.

I can run the same command in VB Demo, which is good to know, but I need to be able to run it as part of my SSIS script.  Any ideas as to what I'm doing wrong?

2) Using the workaround above (updating salesorderline via the sp in VB Demo), it reads my table very very quickly (20,000 rows in about 45 seconds.  However, it throws an error after it finishes reading:

[Source - Query 1 1 [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object.    at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

[DTS.Pipeline] Error: The PrimeOutput method on component "Source - Query 1 1" (1) returned error code 0x80004003 .  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Task Data Flow Task - SalesOrderLine failed

A number of stored procedures don't currently work when executed in a linked MS SQL Server and even MS Access.

Error 80004003 is an Invalid Pointer due to a NULL pointer being returned but you haven't shown me what you were actually running to get the error to guess what's causing the error.