June 2, 2008 at 9:07 am
I have a table loaded from a third party application into a staging area. Certain rows are missing from the imported table, and I'm struggling to see why. I don't think it's a SQL issue but am at a loss as to what else it could be.
The SQL to load the table is (modified from a SELECT INTO and ORDER BY added to easily search for known missing rows):
SELECT * FROM OPENQUERY(EBIS, 'SELECT objID, objOriginator, objType, objUnique, parentUnique, RoleID, rtgStageName, uuserid, fld01,
fld02, fld03, LEFT(fld04, 2442) AS fld04, fld05, fld09, fld10, fld11, fld12, fld13, LEFT(fld14, 2442) AS fld14, LEFT(fld15, 2442) AS fld15, fld16, fld17,
fld18, fld19, fld20, fld21, fld22, fld23, fld26, fld27, fld29, fld31, fld32, fld33, fld34, fld35, fld36, fld37, fld38, fld39, fld40, fld41, fld42, fld43, fld44,
fld45, fld46, fld47, fld48, fld49, fld50, fld51, fld52, fld53, fld54, fld55, fld56, fld57, fld58, fld59, fld60, fld61, fld63, fld64, fld65, fld66, fld67, fld68,
fld69, fld70, fld71, fld72, fld73, fld74, fld75, fld76, fld77, fld78, fld79, fld80, fld81, fld82, fld83, fld84, fld85, fld87, fld88, fld89, fld90, fld91, fld92,
fld94, fld95, fld96, fld97, fld99, fld101, fld102 FROM PUB.objItem WHERE objType = ''dev_request''') ORDER BY objID DESC
The result set is broadly as expected and no errors are reported. However certain rows (identified by objID) are not returned; one of these is objID = 1886. However, if I modified the query to restrict to this particular row then it returns the result!
SELECT * FROM OPENQUERY(EBIS, 'SELECT objID, objOriginator, objType, objUnique, parentUnique, RoleID, rtgStageName, uuserid, fld01,
fld02, fld03, LEFT(fld04, 2442) AS fld04, fld05, fld09, fld10, fld11, fld12, fld13, LEFT(fld14, 2442) AS fld14, LEFT(fld15, 2442) AS fld15, fld16, fld17,
fld18, fld19, fld20, fld21, fld22, fld23, fld26, fld27, fld29, fld31, fld32, fld33, fld34, fld35, fld36, fld37, fld38, fld39, fld40, fld41, fld42, fld43, fld44,
fld45, fld46, fld47, fld48, fld49, fld50, fld51, fld52, fld53, fld54, fld55, fld56, fld57, fld58, fld59, fld60, fld61, fld63, fld64, fld65, fld66, fld67, fld68,
fld69, fld70, fld71, fld72, fld73, fld74, fld75, fld76, fld77, fld78, fld79, fld80, fld81, fld82, fld83, fld84, fld85, fld87, fld88, fld89, fld90, fld91, fld92,
fld94, fld95, fld96, fld97, fld99, fld101, fld102 FROM PUB.objItem WHERE objType = ''dev_request'' AND objID = 1886') ORDER BY objID DESC
That doesn't make sense! How can this row not be in the first result set, yet appear without issue in the second? All I have done is add a further constraint, i.e. the second result set should be a subset of the first. This is what leads me to believe it must be a linked server problem.
Any ideas?
The linked server points at a Progress 4GL database which can be a nightmare to work with, so it may be a Progress issue but usually if that's the case it throws some kind of utterly meaningless error (but at least that's a start). Using SQL Server 2000 SP4 on Windows 2000 Server.
June 2, 2008 at 10:28 am
Update...
One of the ranges missing is objID between 1886 and 1893 (inclusive). I can select individual rows, and ranges of rows, as a subset, between 1886 and 1892 but any select trying to return 1893, or a range including 1893, produces the following error:
[font="Courier New"]Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].[/font]
NB there is still no error when the query runs without any further restrictions, it just excludes the range 1886 - 1893, plus the other range (see below). I cannot see anything wrong with the data looking in the front end of the application in question, and unfortunately have no way to directly query the back end.
Furthermore...the other range we're aware is missing is 1867 to 1884. Unlike the other range, I can return all of this without error (but it still doesn't show in main result set, only the subset). I can return 1867 thru 1892, but as soon as I extend this to 1893 it throws the same error (ditto for any number higher than 1893, so it's whenever 1893 is in the range). Remove the range restriction entirely, and you're back to no error.
Change the range restriction to >= 1800 and it works, but with the range 1850 - 1893 missing (no error).
Change the range restriction to >= 1500 and it works, but with the range 1851 - 1893 missing (again no error).
Change the range restriction to >= 1000 and it works, but with the range 1854 - 1893 missing!!! :w00t:
Last few...
>= 500 results in 1862 - 1893 missing.
>= 1 results in 1868 - 1893 missing. However, removing this (theoretically the same thing as no objID < 1) returns the same range missing, but with 1885 randomly thrown in.
What on earth is going on!? :hehe:
June 2, 2008 at 11:01 am
From your post 1:
Paul Cresham (6/2/2008)
The linked server points at a Progress 4GL database which can be a nightmare to work with, so it may be a Progress issue but usually if that's the case it throws some kind of utterly meaningless error (but at least that's a start).
From your post 2:
Paul Cresham (6/2/2008)
[font="Courier New"]Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].[/font]
This seems self-explanatory...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply