(Component Integration Services only) Allows the user to pass a SQL command buffer to a remote server.
sp_passthru server, command, errcode, errmsg, rowcount [, arg1, arg2, ... argn]
is the name of a remote server to which the SQL command buffer will be passed. The class of this server must be a supported, non-local server class.
is the SQL command buffer. It can hold up to 255 characters.
is the error code returned by the remote server, if any. If no error occurred at the remote server, the value returned is 0.
is the error message returned by the remote server. It can hold up to 1024 characters. This parameter is set only if errcode is a nonzero number; otherwise NULL is returned.
is the number of rows affected by the last command in the command buffer. If the command was an insert, delete, or update, this value represents the number of rows affected even though none were returned. If the last command was a query, this value represents the number of rows returned from the external server.
receives the results from the last row returned by the last command in the command buffer. You can specify up to 250 arg parameters. All must be declared as output parameters.
Returns the date from the Oracle server in the output parameter @oradate. If an Oracle error occurs, the error code is placed in @errcode and the corresponding message is placed in @errmsg, and @rowcount is set to 1:
sp_passthru ORACLE, "select date from dual", @errcode output, @errmsg output, @rowcount output, @oradate output
sp_passthru allows the user to pass a SQL command buffer to a remote server. The syntax of the SQL statement or statements being passed is assumed to be the syntax native to the class of server receiving the buffer. No translation or interpretation is performed. Results from the remote server are optionally placed in output parameters.
Use sp_passthru only when Component Integration Services is installed and configured.
You can include multiple commands in the command buffer. For some server classes, the commands must be separated by semicolons. See the Component Integration Services User’s Guide for a more complete discussion of query buffer handling in passthru mode.
The output parameters arg1 ... argn will be set to the values of corresponding columns from the last row returned by the last command in the command buffer. The position of the parameter determines which column’s value the parameter will contain. arg1 receives values from column 1, arg2 receives values from column 2, and so on.
If there are fewer optional parameters than there are returned columns, the excess columns are ignored. If there are more parameters than columns, the remaining parameters are set to NULL.
An attempt is made to convert each column to the datatype of the output parameter. If the datatypes are similar enough to permit implicit conversion, the attempt will succeed. For information on implicit conversion, see Chapter 2, “Transact-SQL Functions” of Reference Manual: Building Blocks. See the Component Integration Services Users Guide for information on which datatype represents the datatypes from each server class when in passthru mode.
Any user can execute sp_passthru. Permission checks do not differ based on the granular permissions settings.
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 |
|
System procedures sp_autoconnect, sp_remotesql
