sp_remotesql

(Component Integration Services only) Establishes a connection to a remote server, passes a query buffer to the remote server from the client, and relays the results back to the client.

Syntax

sp_remotesql server, query [, query2, ... , query254]

Parameters

server – is the name of a remote server defined with sp_addserver.
query – is a query buffer a with maximum length of 255 characters.
query2 … query254 – is a query buffer with a maximum length of 255 characters. If supplied, these arguments are concatenated with the contents of query1 into a single query buffer.

Examples

Example 1 – Passes the query buffer to FREDS_SERVER, which interprets select @@version and returns the result to the client. The SAP ASE server does not interpret the result:
```
sp_remotesql FREDS_SERVER, "select @@version"
```
Example 2 – Uses sp_remotesql in a stored procedure. This example and Example 1 return the same information to the client:
```
create procedure freds_version 
as 
exec sp_remotesql FREDS_SERVER, "select @@version" 
go
exec freds_version
go
```
Example 3 – Concatenates two query buffers into a single buffer, and passes the complete insert statement to the server DCO_SERVER for processing. The syntax for the insert statement is a format that DCO_SERVER understands. The returned information is not interpreted by the server. This example also examines the value returned in @@error.
```
sp_remotesql DCO_SERVER, 
"insert into remote_table 
(numbercol,intcol, floatcol,datecol )",
"values (109.26,75, 100E5,'10-AUG-85')"
select @@error
```

Example 4 – Illustrates the use of local variables as parameters to sp_remotesql:

declare @servname varchar(30)
declare @querybuf varchar(200)
select @servname = "DCO_SERV"
select @querybuf = "select table_name 
	from all_tables 
	where owner = 'SYS'" 
exec sp_remotesql @servname, @querybuf

Usage

There are additional considerations when using sp_remotesql:

sp_remotesql establishes a connection to a remote server, passes a query buffer to the remote server from the client, and relays the results back to the client. The local server does not intercept results.
You can use sp_remotesql within another stored procedure.
The query buffer parameters must be a character expression with a maximum length of 255 characters. If you use a query buffer that is not char or varchar, you get datatype conversion errors.
sp_remotesql sets the global variable @@error to the value of the last error message returned from the remote server if the severity of the message is greater than 10.
If sp_remotesql is issued from within a transaction, the SAP ASE server verifies that a transaction has been started on the remote server before passing the query buffer for execution. When the transaction terminates, the remote server is directed to commit the transaction. The work performed by the contents of the query buffer is part of the unit of work defined by the transaction.

If transaction control statements are part of the query buffer, it is the responsibility of the client to ensure that the transaction commit and rollback occur as expected. Mixing Transact-SQL with transaction control commands in the query buffer can cause unpredictable results.
The local server manages the connection to the remote server. Embedding connect to or disconnect commands in the query buffer causes results that require interpretation by the remote server. This is not required or recommended. Typically, the result is a syntax error.

See also connect to...disconnect in Reference Manual: Commands.

Permissions

Any user can execute sp_remotesql. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect