SQL Server 2008 R2
Two computers: MachineOne and MachineTwo
Two instances of SQL server,: MachineOne.ServerOne and MachineTwo.ServerTwo
Two databases:MachineOne.ServerOne.DBOne, and Machine2.Server2.DBTwo.
On [MachineTwo].[ServerTwo].[DBTwo] I write an sp that calls an sp from [MachineOne].[ServerOne].[DBOne]
EXEC [MachineOne].[ServerOne].[DBOne].[dbo].[MySP]
[MySP] does a bunch of validation and uses RAISEERROR to break when something fails.
When everything is right as expected it works fine. I want to trap an error in MySP, though, so on ServerOne I call
BEGIN TRY
EXEC [MachineOne].[ServerOne].[DBOne].[dbo].[MySP]
END TRY
BEGIN CATCH