Hi Everyone,
I am trying to diagnose a problem we are having with failing queries. We have three SQL servers that each are responsible for databases for individual applications. Each application is designed to interface with the other so at times, especially in report writing, there is a need to bridge this information together.
The installation of these servers made one SQL server the primary SQL server in which all queries are executed against. This issue relates to executing a query on a SQL 7 server that executes queries on a linked SQL 2000 server.
Pseudo-Query Executed on SQL 7 Server:
SELECT FROM Local Tables INNER JOIN Remote Queries
Pseudo-Execution Plan on SQL 7 Server:
SQL 7 Server -> Remote Query -> SQL 2000 Server
Executing the query in Query Analyzer shows that the query is failing intermittently. From using profiler, I have traced two different Exception errors that are unfortunately unhelpful. They are:
Exception 7330 - Could not fetch a row from OLE DB provider '%ls'. %ls
Exception 7320 - Could not execute query against OLE DB provider '%ls'. %ls
As I said, the problem is intermittent, you may be able to execute the query up to 10 times before having a single failure, or after 10 tries, you may never see the results. One thing is true, the query failure only occurs when querying data through a linked server. Local queries on each server results in no problems.
The server health and stability, as well as network infrastructure has been checked out fine. The servers are very high end dual processor machines with 3 gigs of memory. The applications design to work with each database independently are not having any problems reading and writing to the database.
If I execute a query designed to be executed remotely on the SQL 2000 server, the queries execute fine and returns the results in under a second. However, if I execute the query on the SQL 7 server linking to the SQL 2000 server, this can result with the problem explained above.
Has anyone ever heard of a problem like this occurring and know of any possible solutions? Does anyone have any suggestions or further methods of diagnosing this problem?
Thanks,
Jeremy
SQL @@VERSION dumps:
SQL 7:
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86) Oct 24 2000 18:39:12 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
SQL 2000:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
I am trying to diagnose a problem we are having with failing queries. We have three SQL servers that each are responsible for databases for individual applications. Each application is designed to interface with the other so at times, especially in report writing, there is a need to bridge this information together.
The installation of these servers made one SQL server the primary SQL server in which all queries are executed against. This issue relates to executing a query on a SQL 7 server that executes queries on a linked SQL 2000 server.
Pseudo-Query Executed on SQL 7 Server:
SELECT FROM Local Tables INNER JOIN Remote Queries
Pseudo-Execution Plan on SQL 7 Server:
SQL 7 Server -> Remote Query -> SQL 2000 Server
Executing the query in Query Analyzer shows that the query is failing intermittently. From using profiler, I have traced two different Exception errors that are unfortunately unhelpful. They are:
Exception 7330 - Could not fetch a row from OLE DB provider '%ls'. %ls
Exception 7320 - Could not execute query against OLE DB provider '%ls'. %ls
As I said, the problem is intermittent, you may be able to execute the query up to 10 times before having a single failure, or after 10 tries, you may never see the results. One thing is true, the query failure only occurs when querying data through a linked server. Local queries on each server results in no problems.
The server health and stability, as well as network infrastructure has been checked out fine. The servers are very high end dual processor machines with 3 gigs of memory. The applications design to work with each database independently are not having any problems reading and writing to the database.
If I execute a query designed to be executed remotely on the SQL 2000 server, the queries execute fine and returns the results in under a second. However, if I execute the query on the SQL 7 server linking to the SQL 2000 server, this can result with the problem explained above.
Has anyone ever heard of a problem like this occurring and know of any possible solutions? Does anyone have any suggestions or further methods of diagnosing this problem?
Thanks,
Jeremy
SQL @@VERSION dumps:
SQL 7:
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86) Oct 24 2000 18:39:12 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
SQL 2000:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
