Chapter 1: System Procedures

sp_remotesql

Description

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. Adaptive Server does not interpret the result:

sp_remotesql FREDS_SERVER, "select @@version"

Example 2

Illustrates the use of 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

The server 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

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 will receive 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, Adaptive 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.

Permissions

Any user can execute sp_remotesql.

Auditing

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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