Weird OpenQuery Behavior
-
Hey gang,
This is my first post to this forum, so go easy on me.
I've got a Linked Server set up to an AS400. The connection is via an ODBC DSN. The DSN has 'Always Scrollable' checked and Record Blocking disabled.
I'm able to pull back records using simple SELECT statements with no problem. If I add a couple elements of complexity, however, I only get the TOP one record returned.
EXAMPLE:
SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM Library.SomeTable WHERE UserID = ''d00d''')
This works. I get scads of records. All is right with the world.
SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM Library.SomeTable WHERE UserID = ''d00d''
AND EnteredDate > ''1/31/2005'' AND EnteredDate < ''3/1/2005''') This doesn't. I get back one record. If I run the same thing in Access using a Passthrough query and the same DSN, I get my expected results. Anyone seen anything remotely similar? Any help is greatly appreciated. Thanks, Race
-
Race, try putting the where clause outside the OPENQUERY() like this:
SELECT * FROM
OPENQUERY(AS400, 'SELECT * FROM Library.SomeTable )
WHERE UserID = 'd00d'
AND EnteredDate > '1/31/2005' AND EnteredDate < '3/1/2005'
dab
-
I would use this as a last resort as sql server would input the full table before applying the where condition.
-
Interesting idea! I gave this a shot and I did get multiple records back, but it took 37 minutes to run the query.
So I'm open to further input.
-
That was my point
.The most likely explanation is that AS400 doesn't understand exactly what you want to filter in the where condition. I had one case where I was using something like this :
openquery(..., 'Select * from tbl where col like '''abc%''') and I wasn't getting what I expected. It turned out that I had to use like '"abc%"' to make the linked server undestand my query. Maybe if you replace the "s with 's in your query, you'll get what you want.
-
Forgot to mention this... the first query will run faster (once fixed) because AS400 will be filtering out the results using its own indexes instead of sending out a few Gigabytes of data over the network and letting sqlserver doing all the work without proper indexes.
-
Just for the record I knew the first response's idea would probably take far too long. I was, however, willing to give it a shot in the spirit of experimentation.
That said, I tinkered with your suggestion without positive results. If UserID = ''dood'' by itself worked it didn't make sense that adding additional criteria would cause it to fail.
So I switched providers in the Linked Server and voila! It works. Instead of using an ODBC-based provider I switched to "IBM DB2 UDB for iSeries OLE DB".
I thank you all for your help. It was greatly appreciated!!!
Race
-
Damn, forgot to make you check the provider, it's always a good place to check.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply