Server Access to an Open Data Services Server Application

SQL Server database servers communicate with Open Data Services server applications by using remote stored procedure calls, the same method used to communicate with other SQL Servers. When making a remote stored procedure call, SQL Server automatically initiates a server-to-server connection with Open Data Services, as a client of the Open Data Services remote server.

SQL Server can access an Open Data Services server application as a remote server in the following ways:

• A SQL Server stored procedure can include a call to a remote stored procedure in an Open Data Services server application. For example, a client can execute a stored procedure on SQL Server, causing the client-initiated procedure to execute a remote stored procedure.
• A SQL Server table can contain a trigger that calls a remote stored procedure in an Open Data Services server application. For example, a client update to the table might fire a trigger, causing the trigger to execute a remote stored procedure.
• SQL Server can receive a remote stored procedure call from a client application in which the client specifies a server name that refers to an Open Data Services server application. SQL Server forwards the request to the Open Data Services server application.

In all these cases, the client initiates the action taken by SQL Server. Any returned data is sent by the Open Data Services server application to SQL Server. SQL Server, in turn, passes all results sets on to the client. A SQL Server trigger or stored procedure can retain the values of any returned stored procedure output parameters for later use.

To enable this type of access, you must:

• Configure the computer on which SQL Server is running to connect to the Open Data Services server application as a client of the application. Use the SQL Client Configuration Utility to do this. Use the Advanced option of the SQL Client Configuration Utility if the Open Data Services application is using a non-default process identifier.
• Configure SQL Server itself by using standard SQL Server administrative procedures (such as sp_addserver) to add the Open Data Services server application as a SQL Server remote server.

For Open Data Services functions that are added to SQL Server as extended stored procedures, there are no special configuration issues. For information on the SQL Server system procedures that install extended stored procedures, see the Microsoft SQL Server Administrator's Companion.