drop-route-transact-sql.md

title	DROP ROUTE (Transact-SQL)
description	DROP ROUTE (Transact-SQL)
author	WilliamDAssafMSFT
ms.author	wiassaf
ms.date	03/06/2017
ms.service	sql
ms.subservice	t-sql
ms.topic	reference
f1_keywords	DROP ROUTE DROP_ROUTE_TSQL
helpviewer_keywords	dropping routes DROP ROUTE statement deleting routes routes [Service Broker], removing removing routes
dev_langs	TSQL

DROP ROUTE (Transact-SQL)

[!INCLUDE sql-asdbmi]

Drops a route, deleting the information for the route from the routing table of the current database.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

DROP ROUTE route_name  
[ ; ]  

Arguments

route_name
The name of the route to drop. Server, database, and schema names cannot be specified.

Remarks

The routing table that stores the routes is a metadata table that can be read through the catalog view sys.routes. The routing table can only be updated through the CREATE ROUTE, ALTER ROUTE, and DROP ROUTE statements.

You can drop a route regardless of whether any conversations use the route. However, if there is no other route to the remote service, messages for those conversations will remain in the transmission queue until a route to the remote service is created or the conversation times out.

Permissions

Permission for dropping a route defaults to the owner of the route, members of the db_ddladmin or db_owner fixed database roles, and members of the sysadmin fixed server role.

Examples

The following example deletes the ExpenseRoute route.

DROP ROUTE ExpenseRoute ;  

See Also

ALTER ROUTE (Transact-SQL)
CREATE ROUTE (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.routes (Transact-SQL)