Checking a linked table on another server

rsaritzky

I have a linked table on another server - it is used for inquiry only on a Navision form.

I don't want to cause an error for the user if the other SQL Server is down for any reason. They won't be able to open the linked-table form, but I don't want to crash them out of Navision.

Has anyone discovered or created any code to test if a remote SQL server is available?

Thanks

Ron

kine

May be you can try to use replications. The data will be in sync when the server is on-line, or you will have local copy if the remote server is down... and you do not need to do any code... ;-)

bbrown

Trap the error in your view and return a blank record if there is an error.

rsaritzky

bbrown wrote:

Trap the error in your view and return a blank record if there is an error.

How might you be able to trap the error in the view - the view might not execute because of an unavailable server, but I'm not sure how I could trap for that in the view. Could you give a simple example?

Thx

Ron

bbrown

This is an example that will return a blank record if the table does not exist in the remote database. You could do something similar to test for other conditions by querying the remote system catalogs.

IF  EXISTS (Select object_id from SCALE_A.Tropical.sys.tables Where name = 'Host')
  SELECT PRODNUMB AS [Prod_ Order No_], PALLETNUMB AS [Container ID],
    ITEM AS [Item No_], LOT AS [Lot No_], SUM(QTY) AS Quantity,
      SUM(WEIGHT) AS [Weight from Scale]
  FROM  SCALE_A.Tropical.dbo.Host AS Host_1
  GROUP BY PRODNUMB, PALLETNUMB, ITEM, LOT
ELSE
  SELECT '' AS [Prod_ Order No_], '' AS [Container ID], '' AS [Item No_], '' AS [Lot No_], 0 AS Quantity, 0 
           AS [Weight from Scale]

rsaritzky

bbrown wrote:

This is an example that will return a blank record if the table does not exist in the remote database. You could do something similar to test for other conditions by querying the remote system catalogs.

IF  EXISTS (Select object_id from SCALE_A.Tropical.sys.tables Where name = 'Host')
  SELECT PRODNUMB AS [Prod_ Order No_], PALLETNUMB AS [Container ID],
    ITEM AS [Item No_], LOT AS [Lot No_], SUM(QTY) AS Quantity,
      SUM(WEIGHT) AS [Weight from Scale]
  FROM  SCALE_A.Tropical.dbo.Host AS Host_1
  GROUP BY PRODNUMB, PALLETNUMB, ITEM, LOT
ELSE
  SELECT '' AS [Prod_ Order No_], '' AS [Container ID], '' AS [Item No_], '' AS [Lot No_], 0 AS Quantity, 0 
           AS [Weight from Scale]

Thanks for the sample code - I'm still not convinced that this will not return an error if the remote server is down - but I don't know for sure. I'm going to test it against a test server that I can bring down intentionally and see what happens.

Ron

bbrown

Yes, this example will not cover that situation. This is covering the situation where the table does not exist. I sent this has an example from which you could derive something that works for you.