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

Question

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

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.

• Charlieface: "It is a small stored procedure which should take only a couple of seconds to execute" clearly that's not true. Perhaps parameter sniffing? We'd need to see the procedure, with relevant tables and indexes, and please share the execution plan via brentozar.com/pastetheplan
• SergeyA: Could you confirm that your problem has nothing to do with configuration of linked server and only resulted with slow execution of StoredProcedureBad? How long it takes before "Query timeout expired"? Does increasing this timeout a possible solution?
• Sean Gallardy: "Query timeout expired" is not a permissions issue, you'll want to follow what Charlieface has stated.
• SqlStar: Thank you All! Really appreciate your comments - They helped me narrow down my problem.

Answer 1

"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.

• SqlStar: Thank you All! I increased the timeout time to 1200 seconds and I get the following error back: Msg 701, Level 17, State 123, Procedure LinkedServer.dbo.StoredProcedureBad, Line 64 [Batch Start Line 3] There is insufficient system memory in resource pool 'internal' to run this query. (If anyone has any suggestion for this error please let me know) So, my original question has been answered that there is no permission issue. Something is not working with the stored procedure. Thank you Everyone!!