OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired".

Question

We keep on getting timeout for that particular table only. The 1st query below is the original one . On the 2nd query, we removed the creation of tmp table and directly join it to the HHID table in DB1, however, a timeout is still encountered.

1st query:

IF OBJECT_ID('tempdb..#hhids') IS NOT NULL DROP TABLE #hhids;
CREATE TABLE #hhids(HHID VARCHAR(19));
INSERT INTO #hhids
SELECT HHID
FROM CCPA___HHIDS;
DELETE TH
FROM LINKEDSERV1.USERDB.dbo.CUSTHIST AS CUH
INNER JOIN #hhids AS H
ON H.HHID = TH.HHID;
GO

2nd query:

DELETE TH from LINKEDSERV1.USERDB.dbo.CUSTHIST AS CUH
INNER JOIN CCPA___HHIDS AS H
ON H.HHID = TH.HHID;

Error:

OLE DB provider "SQLNCLI11" for linked server "LINKEDSERV1" returned message "Query timeout expired".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "LINKEDSERV1" reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7306, Level 16, State 2, Line 1
Cannot open the table ""USERDB"."dbo"."CUSTHIST"" from OLE DB provider "SQLNCLI11" for linked server "LINKEDSERV1".

When we run the delete query from USERDB itself, we are not encountering the said timeouts:

Environment:
Both servers are SQL Server 2016 ENT edition.

Answer 1

There is a considerable risk that the optimizer decides to drag the remote table in full into the local server to perform the join. You don't want that to happen.

What I usually do is to package the local table into an XML data, and then pass that as a parameter to sp_executesql on the remote server. Iin the remote batch, I unpack the XML into a temp table to perform the operation.

You have my full understanding if you did not grasp that paragraph on first read - it's certainly not a completely trivial exercise. But I have a longer discussion about the approach here: https://www.sommarskog.se/dynamic_sql.html#remotedata.

Answer 2

Hi @Vijay Kumar ,

Welcome to Microsoft Q&A!

Msg 7399 Query timeout expired

You can reconfigure the timeout setting.
Set the remote login timeout to 30 seconds, by using this code:
sp_configure 'remote login timeout', 30
go
reconfigure with override
go
Set the remote query timeout to 0 (infinite wait), by using this code:
sp_configure 'remote query timeout', 0
go
reconfigure with override
go
For more information, please see: https://support.microsoft.com/en-us/topic/error-message-when-you-execute-a-linked-server-query-in-sql-server-timeout-expired-5efbb55c-bf0a-7ac5-fac4-20ca33da5352
Also, you can execute a DBCC TRACEON (7300, 3604) statement, and then execute the query, you may see additional information in the error message.
If changing the time doesn't help, you can try this method.

Msg 7306

Check the permissions for the account that you are using for the linked server.
Make sure that it at least is a member of the database fixed server role db_datareader.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Vijay Kumar ,

You can try a different approach that guarantees execution on the remote server.

Execute ... At LinkedServer  

In your case, it will be like follows:

EXECUTE(N'DELETE TH from USERDB.dbo.CUSTHIST AS CUH  
INNER JOIN CCPA___HHIDS AS H  
ON H.HHID = TH.HHID;') AT [LINKEDSERV1];  

You may need to adjust the linked server setting: allow RPC, etc.
Useful link: SQL Server: Execute At LinkedServer