Okay, I've been all through this forum trying to figure out what I'm doing wrong, and I'm finally going to post my strange happening....

First off, here's what I'm using:
   - Windows 2003 SP2 32bit
   - QuickBooks Enterprise 2007
   - SQL Server 2005 SP2
   - QODBC Driver 8.00.00.240

I am trying to dump all my QB tables into a SQL database for report creation, no updating, inserting, etc. I have created a linked server in SQL2005 successfully and it can pull data (I can use the sp_report queries just fine). However, when I try and do a SELECT * FROM <anything> I get no data. Let me clarify that a bit...

I'm using the following statement:
     SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Account')
I get nothing from any tables, except BillLinkenTxn, Check, CheckExpenseLine, CreditMemoLinkedTxn, and InvoiceLinkedTxn. For all other tables I receive the message "(0 row(s) affected)" and only get the column header info. (So I know it can see the tables, but not the data.)

Even stranger, if I modify the above statement to read:
    SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Account' UNOPTIMIZED)
I get data back from Account, Bill, BillExpenseLine, etc until I start hitting tables that throw the "Unexpected NULL value was returned..." error.

I've messed around with the Optimizer settings as suggested in many other posts by Tom, but nothing helps. When running the first query, I can see my .OPT file growing, but then nothing gets spit out. (I've deleted the .OPT file many times as well.)

I tried following the steps from the document "Importing Quickbooks into MS SQL Server Utilizing QODBC", but that document was written with SQL2000 and the DTS  that was in SQL2000 has been completely rewritten in SQL2005 to be SISS.

Anyone have a way to get the QB data into a SQL2005 database, or know what's going on with my weird data reads???

Okay, I tried creating a seperate DSN for SQL Server (which I had tried before) and got the same results (as I had before too).

I've created a simple 3 statement SQL query to try different methods:
    SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Account')
    SELECT * FROM QODBC...Account
    SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM Account UNOPTIMIZED')

Only the 3rd statement returns data. The first 2 statements return just column header information.

In regards to the NULL post you directed me to, where in the following statement
    SELECT * FROM OPENQUERY(QODBC,'SELECT * FROM BillItemLine')
would I insert the code you suggested
    Select TxnID, {fn IFNULL(ItemGroupLineItemGroupRefListID, 'NULL')} as ItemGroupLineItemGroupRefID from BillItemLine

Thanks.

Another note on the NULL Value Error with UNOPTIMIZED...  I'm working on each table having to list all the columns and import them individually (in the same statement) by name. However, I've run into a problem in the BillPaymentCheck table with the field IsToBePrinted. Pulling this column with UNOPTIMIZED throws a NULL value error, but when I use your IFNULL function workaround I get the error "Invalid agrument to scalar function: IFNULL". So I can't pull this complete table in any way.

I really need a fix for the optimizer not working. Like I said above, the seperate DSN didn't do the trick.

Single quotes in OPENQUERY need to be two single quotes:

SELECT * FROM OPENQUERY(QODBC,'Select TxnID,
{fn IFNULL(ItemGroupLineItemGroupRefListID, ''NULL'')}
as ItemGroupLineItemGroupRefID from BillItemLine Unoptimized')

I do have two single quotes around the NULL and it works fine with the ItemGroupLineItemGroupRefID query. But the two single quotes don't work with the IsToBePrinted query. I changed to use double quotes and got an error "[QODBC] Column not found: NULL".

IsToBePrinted is not a string, it's either TRUE or FALSE (1,0):

SELECT * FROM OPENQUERY(QODBC,'Select TxnID,
{fn IFNULL(IsToBePrinted, 0)}
as IsToBePrintedFlag from BillPaymentCheck Unoptimized')