Edit

sys.sp_rda_set_query_mode (Transact-SQL)

  • Article

Applies to: SQL Server 2016 (13.x) and later versions

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 SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Transact-SQL syntax conventions

Syntax

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

Arguments

[ @mode = ] @mode

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

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.

Related content