MSSQL Query timeout expired — OLE DB provider “SQLNCLI10” for linked server “[Server Name]” returned message “Query timeout expired”

When I try to transfer data from one database to another via linked server, I have received the following error.

OLE DB provider “SQLNCLI10” for linked server “[Server Name]” returned message “Query timeout expired”.
Msg 7399, Level 16, State 1, Line 35
The OLE DB provider “SQLNCLI10” for linked server “[Server Name]” reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7421, Level 16, State 2, Line 35
Cannot fetch the rowset from OLE DB provider “SQLNCLI10” for linked server “[Server Name]”. .

I have similar issue before [few months ago], but it has been fixed. I have tried several time, I still received the same error, not matter which computer I am running that query from. When I read the error message carefully. I realise it is something to do with the settings on the database.

Here it is the solution.
1) Login to the linked server.[Ensure you have the right to edit the database server settings, otherwise you would need to contact your DBA/System Admin]
2) Then run the following query
sp_configure ‘remote query timeout’, 0  –0 mean no timeout limit, if you would like to give a time limit you can modify the value 0 to something else. I guess it is second.
go

After that you will receive the similar message as below.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ‘remote query timeout (s)’ changed from 600 to 0. Run the RECONFIGURE statement to install.

3)Then you need to run the query below to apply the update.
reconfigure with override
go

[you can run the both query on the same statement like below]
sp_configure ‘remote query timeout’, 0  –0 mean no timeout limit, if you would like to give a time limit you can modify the value 0 to something else. I guess it is second.
go
reconfigure with override
go

Finally, you can try to run the query again and see if it resolve the timeout issue.

Good luck!

Hope you find it useful!

Leave a Reply