In production have two SQL 2012 instances on separate servers, with a Linked Server set up between them. No problems there. In development, I have the databases for both in one 2012 instance, and have a self-referencing Linked Server set up to mimic production.
I have an Agent job that runs every 15 minutes that contains a step that runs a stored procedure. The procedure does a SELECT INTO FROM OpenQuery() to populate a local temp table with data from across the Linked Server. Occasionally, only in development, that job step will hang for almost 4 hours. The error is:
Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer". [SQLSTATE 42000] (Error 7330) OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.
In the Linked Server, I have the query timeout set to 1200, yet the job step will hang for almost 4 hours before getting the timeout. Why isn't the timeeout occurring after 20 minutes per this setting? How can I enforce a timeout on this job, job step, procedure, or statement?