ALTER ROUTE (Transact-SQL)
Modifies route information for an existing route.
ALTER ROUTE route_name WITH [ SERVICE_NAME = 'service_name' [ , ] ] [ BROKER_INSTANCE = 'broker_instance' [ , ] ] [ LIFETIME = route_lifetime [ , ] ] [ ADDRESS = 'next_hop_address' [ , ] ] [ MIRROR_ADDRESS = 'next_hop_mirror_address' ] [ ; ]
The routing table that stores the routes is a meta-data table that can be read through the sys.routes catalog view. The routing table can only be updated through the CREATE ROUTE, ALTER ROUTE, and DROP ROUTE statements.
Clauses that are not specified in the ALTER ROUTE command remain unchanged. Therefore, you cannot ALTER a route to specify that the route does not time out, that the route matches any service name, or that the route matches any broker instance. To change these characteristics of a route, you must drop the existing route and create a new route with the new information.
When a route specifies 'TRANSPORT' for the next_hop_address, the network address is determined based on the name of the service. SQL Server can successfully process service names that begin with a network address in a format that is valid for a next_hop_address. Services with names that contain valid network addresses will route to the network address in the service name.
The routing table can contain any number of routes that specify the same service, network address, and/or broker instance identifier. In this case, Service Broker chooses a route using a procedure designed to find the most exact match between the information specified in the conversation and the information in the routing table.
To alter the AUTHORIZATION for a service, use the ALTER AUTHORIZATION statement.
A. Changing the service for a route
The following example modifies the ExpenseRoute route to point to the remote service //Adventure-Works.com/Expenses.
ALTER ROUTE ExpenseRoute WITH SERVICE_NAME = '//Adventure-Works.com/Expenses'
B. Changing the target database for a route
The following example changes the target database for the ExpenseRoute route to the database identified by the unique identifier D8D4D268-00A3-4C62-8F91-634B89B1E317.
ALTER ROUTE ExpenseRoute WITH BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89B1E317'
C. Changing the address for a route
The following example changes the network address for the ExpenseRoute route to TCP port 1234 on the host with the IP address 10.2.19.72.
ALTER ROUTE ExpenseRoute WITH ADDRESS = 'TCP://10.2.19.72:1234'
D. Changing the database and address for a route
The following example changes the network address for the ExpenseRoute route to TCP port 1234 on the host with the DNS name www.Adventure-Works.com. It also changes the target database to the database identified by the unique identifier D8D4D268-00A3-4C62-8F91-634B89B1E317.
ALTER ROUTE ExpenseRoute WITH BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89B1E317', ADDRESS = 'TCP://www.Adventure-Works.com:1234'