Linked server issue

We are utilizing a linked server connection to a proprietary database server in SQL 2005. Connectivity is thru an ODBC driver written by the proprietary database server's vendor. This has been running successfully for several years utilizing SQL 2000, however we are experiencing problems with 2005. It appears that the proprietary database server can only answer queries single-threaded, or in other words, it must complete a query before it can begin another. Whenever the SQL 2005 server sends multiple queries to the linked server before it has a chance to respond, the connection freezes until the SQL 2005 server can be restarted. As support for the proprietary database server is limited, we are trying to identify if there is a way in SQL 2005 to queue the connections to the linked server to prevent this "traffic jam?" Adjusting connection timeouts only reduces the frequency of the "freezes" and we really need to find a solution to this.

Any ideas, short of writing a custom ODBC driver, would be appreciated.

Thanks in advance.

If you are operating on data within SQL server using data from this linked database, you could wrap that functionality in a transaction to enforce serialization and ensure that this strange single threaded server gets only one connection at a time. This would also provide you with transactional logging to ensure that you handle any other errors that come up without data corruption or loss.

Hope this helps,

John

|||This *could* work but I was hoping for a more global solution as the server is passed a lot of Adhoc queries via OLE and ODBC.|||

You could write a stored procedure if you have a common workload and implement your transactions within it to provide similar service across network stacks. Realistically, any approach to serializing an inherently distributed operation will seem nasty, and the real issue is that any operation involving that linked server is non-reentrant.

Typically, non-reentrant code is very dangerous to threaded code, in that global shared state within the non-reentrant function can nuke the results from another. You can skirt this by wrapping all operations that use that function or server in one big critical section, to avoid another connection coming in and stepping all over the shared state; however, this comes at a considerable performance cost. In the long run, this particular service (if it is used extensively and broadly enough) will become a major bottleneck to the performance of your pipeline. There really is no right answer to how to address this problem other than to either upgrade that driver to a multithreaded one, remove that service from your pipeline, or if possible -- replace real time communication with storage to a table in SQL Server with a batch job running nightly to send data across to synchronize the services. If SQL Server is the consumer of data in this relationship, rather than the producer, or if you have a need for real-time updates to and from the linked server then of course this would not address your issue.

I sympathize with your situation, and hope this helps.

John