INF: Moving Extended Stored Procedures Out Of Process

ID: Q243428

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5, 7.0

SUMMARY

The Open Data Services API allows customers to write extended stored procedures to extend the functionality of SQL Server. When a user runs one of these procedures, SQL Server loads the DLL containing the code and calls the function that was specified when the extended procedure was added. This code runs within the address space of SQL Server.

Because extended stored procedures run within the address space of the server and can access any memory allocated by the process, a poorly written extended stored procedure can potentially cause a number of unpredictable problems with SQL Server. Such problems could be caused by walking off the end of an array (into the memory used by some other SQL Server data structure), or it could be caused by something like a memory leak, double-freeing memory and causing heap corruption, and so forth.

If you are using custom extended stored procedures and are encountering various unpredictable problems, it may be desirable to set these up so that they run on a surrogate server. This will quickly reveal whether the problem is specific to SQL Server or is caused by the extended stored procedure. If one of the extended stored procedures is problematic, it will also bring stability back to the production server until the extended stored procedure developer can identify and resolve the code problem.

SQL Server supports the use of server-to-server remote procedure calls (RPCs), allowing you to use a connection to one server to call a procedure on a secondary server. This article discusses a method of using RPCs to call any custom extended stored procedure on a secondary server without changing any application code.

MORE INFORMATION

The call to an extended stored procedure may occur in two different ways:

Ad-hoc T-SQL.

Through a call to a "wrapper" stored procedure.

In either case, it is possible to 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 T-SQL stored procedure, it is possible to drop the reference to the existing stored procedure and replace it with a wrapper that just 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 same method outlined below to provide an additional layer of indirection on the extended stored procedure call.

For the sake of example, this article uses the xp_diskfree sample extended stored procedure that ships as part of 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. For simplicity, this example also demonstrates having all users on the production server mapped to a single login on the remote server under which the extended procedure is invoked. You can also configure this so that the user context is preserved on the remote server; if this is desired, see the documentation on sp_addremotelogin in SQL Server Books Online for full details.

The process of redirecting the call to a remote server involves removing the existing extended stored procedure entry and creating a T-SQL stored procedure with the same name and parameters. On the production server, you will need to add an entry for the remote server, and vice versa on the remote computer. The following sample code indicates the required steps. Substitute the appropriate server names for the production server (PRODSRV) and the remote server (REMOTESRV).


--Logged in 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 in 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 that this requires that you 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, then further debugging will be required to isolate the cause.

REFERENCES

The more information, see the following articles Microsoft Knowledge Base:

Q190987 INF: Extended Stored Procedures: What Everyone Should Know

Q163449 INF: Use of Thread Local Storage in an Ext. Stored Procedure

Additional query words: xproc sp_helpextendedproc ODS

Keywords : kbSQLServ600 kbSQLServ650 kbSQLServ700 kbCodeSnippet
Version : winnt:6.0,6.5,7.0
Platform : winnt
Issue type : kbinfo