November 18, 2014 at 6:38 am
I wrote an script that connect to 3 servers and get data.
If one of these servers be unavailable, my script generate error and stop.
With Try catch I cant Control it.
please Help me.
November 18, 2014 at 7:51 am
sm_iransoftware (11/18/2014)
I wrote an script that connect to 3 servers and get data.If one of these servers be unavailable, my script generate error and stop.
With Try catch I cant Control it.
please Help me.
A connection error is a session terminating one. As a result - the context that the TRY...CATCH is running in is not there to "catch" anything.
As a result you need to encapsulate the script (in a stored procedure usually), then put a TRY...CATCH around the EXEC statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2014 at 1:31 am
Thank You For Your Guidness
this is the final result :
1- Check Connection With linked Server
Declare @LinkedServerName nvarchar(128)
Declare @retval int
Set @LinkedServerName='YourLinkedServerName'
begin try
exec @retval = sys.sp_testlinkedserver @LinkedServerName;
--Print 'OK'
end try
begin catch
set @retval = sign(@@error);
--Print @retval
end catch;
[/size]
2- It's Better Put This in an storedPrcedure
Create Procedure Sp_CheckLinkedserverConnection
@LinkedServerName nvarchar(128),
@retval int OutPut
As
Begin
begin try
exec @retval = sys.sp_testlinkedserver @LinkedServerName;
end try
begin catch
set @retval = sign(@@error);
end catch;
End
3- After That You Should Bring Your main Code. You Must hidden your linked server Name inan Function or Procedure Such This :
Create Function dbo.RunOnRemoteServer(@AParameter char(50))
Returns @OutPutResult Table (OrderID int,
CustomerName varchar(20),
OrderTotal money)
as
Begin
insert into @OutPutResult
select Orderid,CustomerName,OrderTotal from [LinkedServerName].RowLevel.dbo.Orders
Return
End
4- There is a tip : Don't Forget that Complie Your Function when Your Connection to linked server is OK.
5- Sample Of use this Func.
Declare @IsConnectionError int
Set @IsConnectionerror=0
EXEC Sp_CheckLinkedserverConnection 'YourLinkedServerName', @IsConnectionError OUTPUT
If @IsConnectionError =0
Begin
select * from dbo.RunOnRemoteServer('ParameterValue')
End
Else
select 'Error on Connection'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply