When executing a stored procedure across a linked server, the procedure appears to time-out but no error message returned.
I have two SQL 2012 Standard servers with linked servers defined between the two. I have enabled RPC and RPC Out on both linked servers. When calling a procedure on Server 2 from Server 1 (i.e. "execute [Server-2].[DB].[Schema].[procedure] @sError = @sReturn out;") , we sometimes experience what appears to be a time-out in that the execution of the procedure on Server 2 does not complete but the calling procedure on Server 1 appears to think it finished successfully. The procedure on Server 2 seems to just stop running in the middle of execution without any errors reported via various try-catch blocks. This seems to happen when large amounts of data are being processed via the procedure on Server 2, but finishes successfully when smaller data sets are being processed. Anyone have any advice? Thanks.