Using the sp_sqlexec Built-in Stored Procedure

The sp_sqlexec built-in stored procedure for Open Data Services provides a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application.

The sp_sqlexec stored procedure call uses the following syntax:

sp_sqlexec "query_string"

where

query_string

Is a single database query that you want to send to a destination data source that is connected to an Open Data Services server application. The string can have a maximum of 256 characters, and must be of a char or varchar datatype. You must enclose the string in quotation marks.

The sp_sqlexec built-in stored procedure accepts a user-specified string as a stored procedure argument. SQL Server can pass the stored procedure call to a remote Open Data Services server without performing any processing on the string.

When Open Data Services receives the sp_sqlexec request, it converts the request into a language statement so that the string is handled by the Open Data Services language event handler, allowing the Open Data Services server application to pass the string directly to a back-end data source for processing as a language statement.

Many back-end databases cannot process remote stored procedure calls directly. The sp_sqlexec procedure allows the Open Data Services server application to function as a translator, accepting a remote stored procedure from a client or server in the SQL Server environment, and passing it to another data source as a language statement. In this way, SQL Server handles the request as a remote stored procedure, passing it to the Open Data Services server application without interpreting its contents. Any SQL Server - based client application can use sp_sqlexec to send data to or retrieve data from a gateway application, even when the client application is connected to a SQL Server and is not directly connected to the Open Data Services gateway application. Any SQL Server can call sp_sqlexec as a result of a trigger or from within another stored procedure, allowing SQL Server to send a language statement using its standard method of server-to-server remote stored procedure communication.

The following example sends a remote stored procedure call to an Open Data Services server application called odsapp, which has previously been defined as a remote server in the SQL Server sysservers table:

odsapp...sp_sqlexec "select au_lname, au_fname, phone from 
    authors where au_id = '427-17-2319'"

In this example, odsapp could be an Open Data Services gateway to a DB2® database. The sp_sqlexec procedure causes the odsapp language event handler to issue the SELECT statement to the DB2 back-end database and retrieve the results of the query.