Skip to content

sys-sp-rda-set-query-mode-transact-sql.md

Latest commit

 

History

History
77 lines (54 loc) · 3.72 KB

sys-sp-rda-set-query-mode-transact-sql.md

File metadata and controls

77 lines (54 loc) · 3.72 KB
title sys.sp_rda_set_query_mode (Transact-SQL) | Microsoft Docs
description Use sys.sp_rda_set_query_mode to specify if queries against the current Stretch-enabled database and its tables return local and remote data, or local data only.
ms.custom
ms.date 07/25/2022
ms.service sql
ms.reviewer randolphwest
ms.subservice stored-procedures
ms.topic reference
f1_keywords
sys.sp_rda_set_query_mode
sys.sp_rda_set_query_mode_TSQL
dev_langs
TSQL
helpviewer_keywords
sys.sp_rda_set_query_mode stored procedure
ms.assetid 65a0b390-cf87-4db7-972a-1fdf13456c88
author markingmyname
ms.author maghan

sys.sp_rda_set_query_mode (Transact-SQL)

[!INCLUDE sqlserver2016]

Specifies whether queries against the current Stretch-enabled database and its tables return both local and remote data (the default), or local data only.

Important

Stretch Database is deprecated in [!INCLUDE sssql22-md]. [!INCLUDE ssNoteDepFutureAvoid-md]

Topic link icon Transact-SQL Syntax Conventions

Syntax

  
sp_rda_set_query_mode [ @mode = ] @mode   
    [ , [ @force = ]  @force ]

Arguments

[ @mode = ] @mode
Is one of the following values.

  • DISABLED All queries against Stretch-enabled tables fail.

  • LOCAL_ONLY Queries against Stretch-enabled tables return local data only.

  • LOCAL_AND_REMOTE Queries against Stretch-enabled tables return both local and remote data. This is the default behavior.

[ @force = ] @force
Is an optional bit value that you can set to 1 if you want to change query mode without validation.

Return Code Values

0 (success) or >0 (failure)

Permissions

Requires db_owner permissions.

Remarks

The following extended stored procedures also set the query mode for a Stretch-enabled database.

  • sp_rda_deauthorize_db

    After you run sp_rda_deauthorize_db , all queries against Stretch-enabled databases and tables fail. That is, the query mode is set to DISABLED. To exit this mode, do one of the following things.

    • Run sys.sp_rda_reauthorize_db (Transact-SQL) to reconnect to the remote Azure database. This operation automatically resets the query mode to LOCAL_AND_REMOTE, which is the default behavior for Stretch Database. That is, queries return results from both local and remote data.

    • Run sys.sp_rda_set_query_mode with the LOCAL_ONLY argument to let queries continue to run against local data only.

  • sp_rda_reauthorize_db

    When you run sys.sp_rda_reauthorize_db (Transact-SQL) to reconnect to the remote Azure database, this operation automatically resets the query mode to LOCAL_AND_REMOTE, which is the default behavior for Stretch Database. That is, queries return results from both local and remote data.

See Also

sys.sp_rda_deauthorize_db (Transact-SQL)
Stretch Database