sp_execute_remote (Azure SQL Database)
Applies to: Azure SQL Database
Executes a Transact-SQL (T-SQL) 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 query overview (preview) and Reporting across scaled-out cloud databases (preview).
Transact-SQL syntax conventions
Syntax
sp_execute_remote
[ @data_source_name = ] data_source_name
, [ @stmt = ] stmt
[
{ , [ @params = ] N'@parameter_name data_type [ , ...n ]' }
{ , [ @param1 = ] 'value1' [ , ...n ] }
]
[ ; ]
Arguments
[ @data_source_name = ] data_source_name
Identifies the external data source where the statement is executed. See CREATE EXTERNAL DATA SOURCE. The external data source can be of type RDBMS
or SHARD_MAP_MANAGER
.
[ @stmt = ] stmt
A Unicode string that contains a Transact-SQL 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, aren't allowed. Character constants aren't 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'
isn't.
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 ]'
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 more parameter definitions. Every parameter specified in @stmt must be defined in @params. If the Transact-SQL statement or batch in @stmt doesn't contain parameters, @params isn't required. The default value for this parameter is NULL
.
[ @param1 = ] 'value1'
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 aren't required when the Transact-SQL statement or batch in @stmt has no parameters.
n
A placeholder for the values of extra parameters. Values can only be constants or variables. Values can't be more complex expressions such as functions, or expressions built by using operators.
Return code values
0
(success) or non-zero (failure).
Result set
Returns the result set from the first T-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. If the parameters are entered out of order, an error message occurs.
sp_execute_remote
has the same behavior as EXECUTE regarding batches and the scope of names. The T-SQL statement or batch in the @stmt parameter isn't compiled until the sp_execute_remote
statement is executed.
sp_execute_remote
adds an extra 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 in a similar way to sp_executesql.
Examples
A. Basic example
The following example creates and executes a basic SELECT
statement on a remote database.
EXEC sp_execute_remote
N'MyExtSrc',
N'SELECT COUNT(w_id) AS Count_id FROM warehouse';
B. Example with multiple parameters
This example performs the following actions:
Creates a database scoped credential in a user database, specifying administrator credentials for the
master
database.Creates an external data source pointing to the
master
database and specifying the database scoped credential.Executes the
sp_set_firewall_rule
procedure in themaster
database. Thesp_set_firewall_rule
procedure requires three 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';