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.

query2query254

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

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

See also

Commands connect to...disconnect

System procedures sp_addserver, sp_autoconnect, sp_passthru