SUMMARY
This article describes how to use remote procedure calls (RPCs) to call any custom extended stored procedure on a secondary server without changing any application code.
With the
Open Data Services API, you can write extended stored procedures to extend the functionality of SQL Server. When you run one of these procedures, SQL Server loads the DLL that contains the code and calls the function that you specified when you added the extended stored procedure. This code runs in the address space of SQL Server.
Because extended stored procedures run in the address space of the server and can access any memory that the process allocates, a poorly written extended stored procedure can potentially cause a number of unpredictable problems with SQL Server. Such problems might be caused by walking off the end of an array (into the memory that another SQL Server data structure uses), or it might be caused by something such as a memory leak that is double-freeing memory and causing heap corruption, and so on.
If you are using custom extended stored procedures and are you encountering various unpredictable problems, you may want to set these up so that they run on a surrogate server to decide whether the problem is specific to SQL Server or if the extended stored procedure is causing the problem. If one of the extended stored procedures is the problem, it will also bring stability back to the production server until the extended stored procedure developer can identify and resolve the code problem.
back to the top
Call a custom extended stored procedure on a secondary server
SQL Server supports the use of server-to-server RPCs, which allows you to use a connection to one server to call a procedure on a secondary server. This article describes one method to use RPCs to call any custom extended stored procedure on a secondary server without changing any application code.
The call to an extended stored procedure may occur in two different ways:
- Ad-hoc Transact-SQL.
- Through a call to a "wrapper" stored procedure.
In either case, you can redirect the call to a remote server through a SQL Server RPC call without making any changes to the client application.
Because an extended stored procedure is tracked in sysobjects like any other Transact-SQL stored procedure, you can drop the reference to the existing stored procedure and replace it with a wrapper that calls the remote routine. If you already have a wrapper stored procedure, you can either update it to use the RPC syntax, or you can use the method that is described in this article to provide an additional layer of indirection on the extended stored procedure call.
In this example, you will use the
xp_diskfree sample extended stored procedure that in included with the SQL 6.5 Programmer's Tool Kit. For parameters, you pass the drive letter for which you want to get free space information, and an
OUTPUT parameter to return the result. This example also demonstrates having all users on the production server mapped to a single logon on the remote server under which the extended procedure is run. You can also configure this so that the user context is preserved on the remote server; to do so, see the "sp_addremotelogin" topic in SQL Server Books Online.
The process of redirecting the call to a remote server involves removing the existing extended stored procedure entry and creating a Transact-SQL stored procedure with the same name and parameters. On the production server, you must add an entry for the remote server, and on the remote server, you must add an entry for the production server.
back to the top
Code sample
Use the following sample code to redirect the call to a remote server. Substitute the appropriate server names for the production server (PRODSRV) and the remote server (REMOTESRV).
--Logged on as 'sa' on PRODSRV
use master
go
exec sp_addserver REMOTESRV
go
--Drop the xp
exec sp_dropextendedproc xp_diskfree
go
--Replace with stored procedure taking same parameters
create procedure xp_diskfree @drive varchar(3), @space int OUTPUT AS
exec REMOTESRV.master..xp_diskfree @drive, @space OUTPUT
go
grant execute on xp_diskfree to public
go
--Logged on as 'sa' on REMOTESRV
use master
go
--Add new login/user for all remote users to call xp
exec sp_addlogin 'xpuser', NULL, 'master'
exec sp_adduser 'xpuser', 'xpuser'
go
--Add remote server and map all logins from PRODSRV to xpuser
exec sp_addserver PRODSRV
exec sp_addremotelogin PRODSRV, 'xpuser'
go
--Register the extended stored procedure on the server
exec sp_addextendedproc 'xp_diskfree', 'xpsample.dll'
go
grant execute on xp_diskfree to xpuser
go
NOTE: For this code sample, you must first move the extended stored procedure DLL and any supporting files to the remote server.
If the problems move with the extended procedure to the secondary server, you may have to perform additional debugging to isolate the cause of the problem.
back to the top