Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1543

pitfalls of accessing linked server over WAN

Show all headers | View raw

I am planning on accessing remote server from the stored procedure
--
CREATE PROCEDURE [dbo].[GetSTatus] 
        -- input parameters
        @CUST_ID        int, 
        @VALID_DATE     date,
        -- output parameter:
        --      0 = NOT Valid
        --      1 = Valid
        @STATUS         int OUTPUT
AS
BEGIN

SET NOCOUNT ON;
BEGIN TRY
        set @STATUS = 
        case
                when exists
                (
                        select 1 
                        from    remoteserver.dbname.dbo.tblname
                        where   Cust_Id = @CUST_ID
                        and     Fr_Date <= @VALID_DATE 
                        and     To_Date >= @VALID_DATE
                )
                then 1
                else 0
        end

END TRY

BEGIN CATCH
        set @STATUS = 0;
END CATCH

END

--
The WAN is over stable dedicated circuit, but I'd like to hear what are potential problems here, any suggestions on how to code for those situations.

The query will be fired with customer calling in for status, if remote server is not available for whatever reason I need the application to get 0.

I'd like to make sure that any network related issues will not hung the application. 


Any suggestions are welcome.
Thanks in advance,
migurus

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-20 18:31 -0700
  Re: pitfalls of accessing linked server over WAN Erland Sommarskog <esquel@sommarskog.se> - 2013-08-21 07:20 +0000
    Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-21 17:24 -0700
      Re: pitfalls of accessing linked server over WAN Erland Sommarskog <esquel@sommarskog.se> - 2013-08-22 08:48 +0000
  Re: pitfalls of accessing linked server over WAN Lutz <news@invalid.invalid> - 2013-08-21 10:02 +0200
    Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-21 17:21 -0700
      Re: pitfalls of accessing linked server over WAN Lutz <news@invalid.invalid> - 2013-08-22 08:47 +0200
        Re: pitfalls of accessing linked server over WAN bradbury9 <ray.bradbury9@gmail.com> - 2013-08-22 00:10 -0700
        Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-22 11:32 -0700

csiph-web