Skip to content

sp-execute-remote-azure-sql-database.md

Latest commit

 

History

History
110 lines (84 loc) · 6.65 KB

sp-execute-remote-azure-sql-database.md

File metadata and controls

110 lines (84 loc) · 6.65 KB
title sp_execute_remote (Azure SQL Database) | Microsoft Docs
ms.custom
MSDN content
MSDN - SQL DB
ms.date 02/01/2017
ms.prod
ms.reviewer
ms.service sql-database
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
f1_keywords
sp_execute_remote
sp_execute_remote_TSQL
helpviewer_keywords
remote execution
queries, remote execution
ms.assetid ca89aa4c-c4c1-4c46-8515-a6754667b3e5
caps.latest.revision 17
author CarlRabeler
ms.author carlrab
manager jhubbard

sp_execute_remote (Azure SQL Database)

[!INCLUDEtsql-appliesto-xxxxxx-asdb-xxxx-xxx_md]

Executes a [!INCLUDEtsql] statement on a single remote Azure SQL Database or set of databases serving as shards in a horizontal partitioning scheme.

The stored procedure is part of the elastic query feature. See Azure SQL Database elastic database query overview and Elastic database queries for sharding (horizontal partitioning).

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_execute_remote [ @data_source_name = ] datasourcename  
[ , @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

Arguments

[ @data_source_name = ] datasourcename
Identifies the external data source where the statement is executed. See CREATE EXTERNAL DATA SOURCE (Transact-SQL). The external data source can be of type "RDBMS" or "SHARD_MAP_MANAGER".

[ @stmt= ] statement
Is a Unicode string that contains a [!INCLUDEtsql] statement or batch. @stmt must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Note

@stmt can contain parameters having the same form as a variable name, for example: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params= ] N'@parameter_name**data_type [ ,... n ] '
Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. Each parameter definition consists of a parameter name and a data type. n is a placeholder that indicates additional parameter definitions. Every parameter specified in @stmtmust be defined in @params. If the [!INCLUDEtsql] statement or batch in @stmt does not contain parameters, @params is not required. The default value for this parameter is NULL.

[ @param1= ] 'value1'
Is a value for the first parameter that is defined in the parameter string. The value can be a Unicode constant or a Unicode variable. There must be a parameter value supplied for every parameter included in @stmt. The values are not required when the [!INCLUDEtsql] statement or batch in @stmt has no parameters.

n
Is a placeholder for the values of additional parameters. Values can only be constants or variables. Values cannot be more complex expressions such as functions, or expressions built by using operators.

Return Code Values

0 (success) or non-zero (failure)

Result Sets

Returns the result set from the first SQL statement.

Permissions

Requires ALTER ANY EXTERNAL DATA SOURCE permission.

Remarks

sp_execute_remote parameters must be entered in the specific order as described in the syntax section above. If the parameters are entered out of order, an error message will occur.

sp_execute_remote has the same behavior as EXECUTE (Transact-SQL) with regard to batches and the scope of names. The Transact-SQL statement or batch in the sp_execute_remote @stmt parameter is not compiled until the sp_execute_remote statement is executed.

sp_execute_remote adds an additional column to the result set named '$ShardName' that contains the name of the remote database that produced the row.

sp_execute_remote can be used similar to sp_executesql (Transact-SQL).

Examples

Simple example

The following example creates and executes a simple SELECT statement on a remote database.

EXEC sp_execute_remote  
    N'MyExtSrc',  
    N'SELECT COUNT(w_id) AS Count_id FROM warehouse'

Example with multiple parameters

Create a database scoped credential in a user database, specifying administrator credentials for the master database. Create an external data source pointing to the master database and specifying the database scoped credential. Then following, example in the user database, executes the sp_set_firewall_rule procedure in the master database. The sp_set_firewall_rule procedure requires 3 parameters, and requires the @name parameter to be Unicode.

EXEC sp_execute_remote @data_source_name  = N'PointToMaster', 
@stmt = N'sp_set_firewall_rule @name, @start_ip_address, @end_ip_address', 
@params = N'@name nvarchar(128), @start_ip_address varchar(50), @end_ip_address varchar(50)',
@name = N'TempFWRule', @start_ip_address = '0.0.0.2', @end_ip_address = '0.0.0.2';

See Also:

CREATE DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL DATA SOURCE (Transact-SQL)