Hi,
I have a stored procedure that does a query to JDE running in AS400 (I configure this as a linked server). I use OPENQUERY to execute the queries.
I execute the stored procedure from Query Analyzer there is no problem, and the execution time is really fast.
But when I execute the stored procedure through an Access database sitting on a remote PC, it hangs (running sp_who in SQL Server will show the process as "runnable" , "EXECUTE", but never finished). And after this happen, I will not be able to execute the stored procedure from Query Analyzer, it gives me the error "CoCreateInstance of MSDASQL fails" (I have to restart the machine to be able to execute the stored proc again)
When I put raiserrors on the stored proc, the errors can be captured by my Access code, which shows that it is actually executing the stored proc. And if I just use it to execute a normal stored proc (without any queries to AS400), it runs just fine.
if I use the same Access database file, and use it on the same PC as the SQL Server is installed (so the SQL Server is local), no problem.
I hope I describe the problem clear enough... not sure though whether the problem is on SQL Server or on Access.
So anyone has an idea where I went wrong?
Thanks a lot!
alfredp77
I have a stored procedure that does a query to JDE running in AS400 (I configure this as a linked server). I use OPENQUERY to execute the queries.
I execute the stored procedure from Query Analyzer there is no problem, and the execution time is really fast.
But when I execute the stored procedure through an Access database sitting on a remote PC, it hangs (running sp_who in SQL Server will show the process as "runnable" , "EXECUTE", but never finished). And after this happen, I will not be able to execute the stored procedure from Query Analyzer, it gives me the error "CoCreateInstance of MSDASQL fails" (I have to restart the machine to be able to execute the stored proc again)
When I put raiserrors on the stored proc, the errors can be captured by my Access code, which shows that it is actually executing the stored proc. And if I just use it to execute a normal stored proc (without any queries to AS400), it runs just fine.
if I use the same Access database file, and use it on the same PC as the SQL Server is installed (so the SQL Server is local), no problem.
I hope I describe the problem clear enough... not sure though whether the problem is on SQL Server or on Access.
So anyone has an idea where I went wrong?
Thanks a lot!
alfredp77
