I have 2 SQL 2005 servers SRV1 and SRV2. SRV2 is the linked server on SRV1. I run a storep proc with params on SRV2 and it is completed immediately. But when I run the same proc through the linked server on SRV1, for example EXEC [SRV1].DB_TEST.dbo.p_sample_proc it takes about 8-10 minutes to complete. After restarting SRV2 the problem is gone. But some time later it returns. Does anyone have any ideas what it could be?
Might need more rights on SRV2, says Linchi Shea in this article
The login used need to run
Edit: After andomar's comment: what does this do?
SELECT * FROM OPENQUERY ('SRV1', 'EXEC DB_TEST.dbo.p_sample_proc')
In SQL Server Management Studio, check Management -> Activity Monitor on SRV2. That should show you the state of the process that's running p_sample_proc. Maybe it is blocking on a lock from some other process.
It's safe to say that resetting a server removes all locks, and maybe the blocking application takes a while to reconnect to SRV2.