Linked server problem

[mas_oyama]

Ok, we've got a problem. I'ts probably just a tiny configuration problem, but we cant seem to find it...So. The problem id the following:We have SQL Server 2000, and we need to access data from a AS/400. We made a linked server, so we can execute our queries by doing, for example:

SELECT * FROM OpenQuery(JMB400, 'SELECT PPSUPL, PPHCST, PPHDS1 FROM SYNTAXPF."NU.PRAGH"')

So that works, but there is a problem: the error:An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'. [OLE/DB provider returned message: [iBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7973 - SQL create package for SQLSERVNBA in SNYTAXPF has failed.]OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].appears when we try that query. we have to retry the query for every table from the AS/400 in it, then the query passes. so if we have:

SELECT *FROM some_table ST	INNER JOIN OpenQuery(JMB400, 'SELECT * FROM SYNTAXPF."NU.ITEM"') Cost ON ST.Number = Cost.ITEMNO	LEFT  JOIN OpenQuery(JMB400, 'SELECT * FROM SYNTAXPF."NU.PRAGH"') Supplier ON Cost.PPSUPL= Supplier.PPSUPL

But anyway, to run this query correctly, we actually have to run it 3 times:1) the firts time we run it, and...2) the errors pops because of the first table we try to reach, so we run it again, and...3) it blocks on the second table from the AS/400, so we run the query for a third time, and this time, it worksSo i would like to know if someone know a way to solve this problem. when we run it from access, in a form for example, we made a function that auto-retry until the query passes, but if the stored procedure where the query is takes a minute before it reaches the query that causes this problem, then it'll take 3 minutes to execute instead of 1...also, when we finally have the query to pass, we can run it again without any problem... but if we wait for like 10 seconds and try it again, it wont work... we'll have to retry it a couple of time again. so i imagine that if it takes a minute before it is retried again, it could block at the first table again, so the stored proc will never be able to complete, i dont know yet, i havent tried...

[mas_oyama]

bump