Sql Server Ssis Connection

Understanding the Connection is Busy With Results From Another Command Error

If you work with SQL Server, specifically SQL Server 2000, you may encounter a frustrating error: Connection is busy with results for another command. This issue often arises when executing a SQL Server Integration Services (SSIS) package and primarily involves linked server queries. The error can disrupt your workflow and lead to unexpected delays in your processes. This blog post will explore the nature of this error and provide a thorough solution to address it effectively.

What Causes the Error?

The error occurs under certain stress conditions, particularly when executing operations that involve linked servers. Some of the symptoms include:

  • The error message indicates a busy connection and prevents execution.
  • Variations of the error may appear, always including the phrase “Connection is busy with results for another command.”

These symptoms can lead users to confusion, especially when differing behavior is observed with different SQL Server versions (like SQL Server 2005, which does not seem to have this issue).

The Solution

Step 1: Apply the Latest Service Pack

To resolve the Connection busy with results for another command error, your first action should be to ensure that you have the latest service pack installed for SQL Server 2000. This specific issue was initially fixed in SQL Server 2000 Service Pack 4. Here’s how you can do it:

  1. Check Your Current Version: Confirm which SQL Server version and service pack you are currently using.
  2. Download the Latest Service Pack: Visit Microsoft’s official website or use the link to Microsoft KB article 822668 for guidance on obtaining the latest service pack.
  3. Install the Service Pack: Follow the instructions on the Microsoft site to upgrade your SQL Server 2000 instance.

Step 2: Monitor and Manage Linked Servers

After upgrading, it’s essential to monitor the linked servers closely. Here are a few best practices to consider:

  • Limit Concurrent Queries: When possible, try to minimize the number of simultaneous queries being executed against linked servers.
  • Optimize Queries: Ensure that the queries being run are optimized for performance, which may help reduce the likelihood of hitting the busy connection issue.
  • Error Handling: Implement robust error handling in your SSIS packages to gracefully handle any connection issues if they arise after the service pack has been applied.

Additional Resources for Troubleshooting

  • Mark Meyerovich’s Blog Post: You may also find it helpful to read this archived blog post by Mark Meyerovich, which discusses various issues faced with SQL Server and provides personal insights into dealing with the busy connection error.

By taking these steps, you should be able to effectively resolve the Connection is busy with results for another command error and improve the stability of your SQL Server 2000 interactions, especially when using SSIS packages.

Conclusion

While encountering the Connection is busy with results for another command error can be an annoying setback, understanding its root cause and implementing the provided solutions can pave the way for smoother operations in your SQL Server environment. Remember, keeping your software up to date is crucial for maintaining optimal performance and stability.