Extended Stored Procedure – SQL Server 2014

By Admin at 30 Jun 2016, 17:42 PM
  • Introduction

    Stored procedures can be used to perform programming tasks within SQL Server, but these procedures are limited to operations that involve the database alone. You might wonder if you could extend this to work with programs or libraries that are available on the server. For example, if someone became a new subscriber and his record was created in SQL Server, can I email him a preformatted Word document with his name on it? In this case, an extended stored procedure can be used.

    An Extended Stored Procedure (XP) is a DLL (Dynamic Link Library) that extends functions to other Microsoft applications.

    An XP permits us to create our own routines in a simple programming language. The manipulations for this function resemble a normal stored procedure in passing parameters, returning status and results. We can create an XP using our own language since they are programmed using SQL Server Open Data Services API (Application Programming Interface). The DLL created might use APIs to interact, control and request a response from SQL Server. These APIs holds the built-in C/C++ functions and macros to build gateway applications and extended stored procedures in earlier versions.

    Let’s consider a scenario: our application will perform a process based on new records inserted into a table. Normally, we create a database connection that continuously checks and queries for new entries. The drawback of this approach is that the table is repeatedly accessed. An XP adopts an event-based approach instead. An XP written inside the server would be triggered when a new record is inserted.

    Communication with a SQL Server Database via an API

    The client application initially sends an API Request, formatted as a Tabular Data Stream (TDS).

    A TDS is an endpoint SQL Server object that represents the communication between the client and the server remotely.

    The SQL Server network interface protocol layer hides the TDS packet inside standard communication protocol, TCP/IP protocols. The server identifies the TDS and triggers the residing XP by its open data services.

    Finally, a response is served to the client application as an API – Response.

    Communication with a SQL Server Database via an API

    Working Strategy of an Extended Stored Procedure

    When we execute an XP, the request is passed as a tabular data stream (TDS) from the client application to SQL Server via Net-Libraries and Open Data Services.

    We can specify the name of the extended stored procedure and the name of the DLL in which the extended stored procedure function will reside:

    1. sp_addextendedproc ‘xp_average’, ‘xp_average.dll’
    2. average = xp name, average.dll = extended stored procedure DLL

    SQL Server searches to find the DLL associated with the XP, and loads the DLL. Then,p it calls the respective extended stored procedure (a function inside DLL) and generates the resulting sets, returning the parameters to the server through Open Data Services API.

    Creating Extended Stored Procedures

    An extended stored procedure appears as a normal function with the following prototype:

    sp_addextendedproc ‘xp_average’, ‘xp_average.dll’, where xp_ is optional and the xp name is case sensitive. All functions outside of the DLL must be exported, which can be done by listing its name in the EXPORTS section of .def file.

    .

    Let’s examine a code snippet to create an Extended stored procedure DLL in C# - compiled and stored in 'C: \Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\

    1. public class xp_sampleUser
    2. {
    3. staticstring connection String = System.Configuration.ConfigurationManager.ConnectionString ["Connection String"].
    4. ConnectionString.ToString ();
    5. [Microsoft.SqlServer.Server.SqlProcedure]
    6. publicstaticvoid HelloWorld ()
    7. {
    8. SqlContext.Pipe.Send ("Hello world! The Time is" + System.DateTime.Now.ToString () + "\n");
    9. Using (SqlConnection connection = newSqlConnection (connection String))
    10. {
    11. Connection. Open ();
    12. SqlCommand command = newSqlCommand ("SELECT value FROM MSreplication_options where opt name='transactional'", connection);
    13. SqlDataReader reader = command.ExecuteReader ();
    14. SqlContext.Pipe.Send (reader);
    15. }
    16. }
    17. }

    The following Transact-SQL is used to create the procedure in the database, and specifies the OUTPUT parameter.

    1. EXECUTE @return_status = procedure_name
    2. CREATE PROCEDURE HelloWorld (varchar (50) out) AS
    3. BEGIN
    4. PRINT ('Hello world!')
    5. SELECT opt name FROM MSreplication_options where opt name='transactional'
    6. END;
    1. declare @return_status int;
    2. EXECUTE @return_status = HelloWorld

    Extended Stored Procedure

    To add the procedure, we are prompted with sp_addextendedproc ‘xp_average’, ‘xp_average.dll’

    Calling an XP is similar to calling a normal stored procedure, but it remains inside a master database. Therefore, a prefix ‘master’ DB name is mandatory. To demonstrate extended stored procedures, we’ll use an example.

    Sample: To find the free space of a drive

    Signature exec master..xp_fixeddrives

    Extended Stored Procedure 3

    Advantages

    • Create DLL routines as regular Stored Procedures for any SQL Server version.
    • Since they use API-Request/Response formats, they are capable of running remotely.
    • In the case of non–SQL Server based applications, we can create gateways to tie the two programs together.
    • We can interface with various applications, and this function will deliver the result set as a whole.

    Disadvantages

    • The reliability of the server may get reduced by extended stored procedures, sometimes leading to memory leaks.
    • Granting permission to other users is mandatory - system admin reviews are a must after each update.
    • If user data was directly appended as an input, SQL Injection may possible.

    Conclusion

    Extended Stored Procedures are a powerful function - it allows us to easily write an event log, parse xml data, encrypt a file, command line shelling and much more, which are not otherwise possible. They can call functions or methods from a DLL. They allow us to plug in customized functional features into SQL Server. They can be used to execute other programs DLL in remote by API Request/Response.


    Comments

     

    Post a comment

    Please correct the following: