Null Value Error |
Author |
Message |
Ian |
|
Group | : Members |
Posts | : 19 |
Joined | : 2006-05-03 |
|
Profile |
|
Posted : 2006-07-01 07:02:49 |
I received the following error when executing SELECT * FROM QODBC...BillItemLine
An unexpected NULL value was returned for column "[eGroup_QB]...[BillItemLine].ItemGroupLineItemGroupRefListID" from OLE DB provider "MSDASQL" for linked server "eGroup_QB". This column cannot be NULL.
I am running SQL Server 2005 to QB 2006 - it seems to be a case of data corruption - not sure how that is possible.
Ian |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-07-01 09:31:55 |
Sorry, shouldn't the statement be?:
SELECT * FROM eGroup_QB...BillItemLine |
|
|
|
Ian |
|
Group | : Members |
Posts | : 19 |
Joined | : 2006-05-03 |
|
Profile |
|
Posted : 2006-07-01 09:33:22 |
Yes - I was just putting it in the form of your examples - the actual statement I am running has the correct linked server named. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-07-01 09:51:35 |
Please update QODBC to v6.00.00.176 and let me know whether BillItemLine is the only table with this problem? Or are there other tables? Please also check whether the Sample Rock Castle Construction company file has the same problem with BillItemLine or not?
See: How can I get the latest version of QODBC? |
|
|
|
Ian |
|
Group | : Members |
Posts | : 19 |
Joined | : 2006-05-03 |
|
Profile |
|
Posted : 2006-07-01 13:42:48 |
I upgraded QODBC earlier today. I changed the ODBC configuration to point at both the product and service based test files - I received this same error with both files.
This is the first time that I have seen this error in any SQL statements. The query works fine it I do not include that column.
Up until today I had been receiving a generic SQL error stating the pipe had ended and the results were not received from the ODBC "server" (see previous post several months ago). I spent some time over the last couple of months with Microsoft SQL Server support and today we took the ODBC Provider "out of process" which allowed the actual QODBC error messages to appear within SQL Server. Previously any malformed Query would produce this generic error and not give specifics - also, because the dll was executing within the SQL Server process, anytime it had a problem it took the SQL Server down with it. So everyime I had an error querying QODBC, the MSSQL service would stop and immediately restart. I will write up a narrative on this whole issue and send it your way in case anyone else has this problem in the future.
Ian |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-07-01 13:57:29 |
Interesting. BillItemLine.ItemGroupLineItemGroupRefListID is often a NULL because not every inventory item will belong to a group. You will need to subsitute the null value using something along the lines of:
Select TxnID, {fn IFNULL(ItemGroupLineItemGroupRefListID, 'NULL')} as ItemGroupLineItemGroupRefID from BillItemLine
|
|
|
|