On a linked server I can execute a stored procedure A but Can Not execute stored procedure B

Question

I have a db server DBServer and a linked server LinkedServer. From the DBServer I can execute successfully a stored procedure StoredProcedureGood like :

EXEC LinkedServer.DatabaseName.StoredProcedureGood

But for another stored procedure:

EXEC LinkedServer.DatabaseName.StoredProcedureBad

It gives following error: OLE DB provider "MSOLEDBSQL" for linked server "LinkedServer" returned message "Query timeout expired".

It is a small stored procedure which should take only a couple of seconds to execute.

On the LinkedServer, for both stored procedures properties-> Permissions "look" the same to me. One difference is StoredProcedureGood only selects and there are no updates made inside the stored procedure StoredProcedureBad deletes and inserts in a table.

I was able to execute an update using following two statements: UPDATE TOP(1) [LinkedServer].[DatabaseName].dbo.TableName set ParmValue = 11 where parmname= 'A'

EXEC ('UPDATE TOP(1) [LinkedServer].[DatabaseName].dbo.TableName set ParmValue = 11 where parmname= ''A'' ')

How can I get my stored procedure StoredProcedureBad to execute on LinkedServer??

Any suggestions would be greatly appreciated.

score 1 · Accepted Answer · answered May 21 '25 at 16:56

"Query timeout expired" means that your client (DBServer) canceled the request to the linked server. The timeout period can be configured on the linked server, and has a server-wide default.

select s.name, case when s.query_timeout = 0 then c.value else s.query_timeout end query_timeout
from sys.servers s
cross join sys.configurations c
where c.name = 'remote query timeout (s)'

As to why the procedure is taking that long, you'll need to capture the query and query plan at the remote server and troubleshoot.

On a linked server I can execute a stored procedure A but Can Not execute stored procedure B

1 Answers1