2

I'm working on a SQL Server 2008 R2 SP1 with a Sage product registered as a linked server. There are a few stored procedures that execute a pass-through against the linked server. Once in a while, a few sessions hang at the very first DML statement of the entire procedure (SELECT) against the linked server, never get any result back, and hang indefinitely with the OLEDB wait type.

Upon some testing, I found that executing a very simple statement in multiple sessions against it causes a lock up of one or more sessions. A simple statement being anything that uses linked server name like...

SELECT OrderNo   
FROM OPENQUERY(Linked_Server, 
    'SELECT OrderNo 
     FROM Orders
     WHERE OrderNo IS NULL');

and

EXEC ('SELECT OrderNo 
     FROM Orders
     WHERE OrderNo IS NULL') AT Linked_Server 

The strange things are that...

  1. One of the sessions always finishes
  2. The hang sessions usually successfully finish some random number of batches (usually below 10) before it freezes
  3. With the same connection strings, executing the same SQL statement (without OPENQUERY because it's directly against the linked server over ODBC) in a different environment, such as VBScript using ADO, has no issues, even when the number of concurrent sessions is increased (e.g. 10 sessions w/300 executions each instead of 2 sessions w/100 executions).
  4. Profiler shows no error. All hang sessions just stop at one same method IDBInitialize::Initialize with no further processing.
  5. Sage side logs also show no errors or anything out of ordinary. They look normal until the point of freezing, after which the logs look as if the Sage's ODBC driver never received the query from the hung sessions.
  6. I can replicate lock ups at will

I researched and found this page SQL – Openquery (1) Use It Or Not Use It. Because ADO has no issue, and for what's explained in the linked page, I have to deduce that this must be caused by some limitations of linked server. But I can't find any documentations on that at all.

So, here are the questions.

  1. Are there unofficial limitations to Linked Server, like the number of connections or the total size of ALL requests/result and such?
  2. If not, what could be the potential causes of it?
  3. Lastly, is this a bug in 2008, and not in the newer versions?
  • Worth mentioning - SQL Server 2008R2 SP1 is out of support as of 2013/10/08 ! – Kin Shah Nov 3 '15 at 21:21
  • wow, thanks! I'm so stupid for not noticing that for 2 years! Well, I'll be talking to Sage on upgrading since it's integrated with their CRM system. Hopefully, they'd give me a straight up answer... – Kentaro Nov 3 '15 at 23:32
  • IDBInitialize::Initialize that you observed is OLEDB trying to initialize the data source. Linked servers is essentially DQ which uses OLEDB so you're tied to OLEDB limitations. That aside, just to be clear, you hit problems when executing the query via SSMS on Host_A query Host_B via Linked_Server_X. If you ran the same query from some app connecting to Host_A querying Host_B via Linked_Server_X it works fine. Is that correct? If so, are the SSMS sessions from the same connection? Can you try using a different connection in SSMS? – SQLmojoe Nov 6 '15 at 17:41
  • @SQLmojoe, thanks for the input. (To clarify, i edited the question). Using the same naming convention, no, anything that goes via linked_server_x, regardless of sessions or originating app, hit the problem. Would you elaborate on what you mean by a different connection? Additional info i forgot to mention....The SQL instance and Sage are on the same box. And all the scripts in all apps that connect to SQL and execute SPs are executed at server-side. – Kentaro Nov 6 '15 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.