On a linked server I can execute a stored procedure A but Can Not execute stored procedure B
2
votes
1
answer
115
views
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.
Asked by SqlStar
(23 rep)
May 20, 2025, 02:28 PM
Last activity: May 21, 2025, 04:56 PM
Last activity: May 21, 2025, 04:56 PM