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'