SQL Server Extended Procedures

1. SQL Server Extended Procedures Leveraging T-SQL to centralize server functions

2. What are Extended Stored Procedures? • Dynamic extensions to T/SQL • Can be written in any language that can produce DLLs • Allows passing of data back and forth between the XP and the calling SP • Can be a security risk

3. Microsoft-supplied Sample XPs • Available from Query Analyzer online help (Books Online) • Useful reference to start from • Very C-oriented • Requires large quantities of code to accomplish anything useful • No examples of how to issue queries/commands from the XP See Item 1: Microsoft-provided Sample XP

4. softwareAB Extended Procedures • Can be used to perform any functions difficult or impossible in T/SQL • Make transferring files between the client application and the SQL Server easy and convenient • Features extensible generic C++ class to interface with SQL Server

5. Inside the XP • Each DLL should have a version function exported: extern "C" ULONG __GetXpVersion() { //lint !e765 return ABR::SqlServer::version() ; } //__GetXpVersion • Exported functions should avoid C++ name-mangling and always receive a pointer to SRV_PROC: extern "C" __declspec(dllimport) int SomeFuncName(SRV_PROC*);

6. The softwareAB Approach • Segregate SQL-Server specific header files into a single module (helps to avoid namespace clashes). • Avoid C: Develop a C++ class for each supported XP; within the C exported function create a class instance and thunk down immediately. • Avoid multi-threading issues by defining a minimal number of global variables.

7. The C++ Assumptions • softwareAB uses a shallow, cross-platform, multi-threaded C++ wrapper library to handle common functions (pipe interactions, debugging message support, process creation, etc.) • All softwareAB C++ objects within our own DLLs are derived from a common base class. • softwareAB avoid multiple inheritance, threading, and direct memory management except when absolutely required. See Item 2: abr_common and abr_regex

8. SQL Server Integration • Details are hidden using the SqlServer class. • Class makes it easy to accept parameters from SP callers and to return result sets. • All SQL Server data types are hidden via typedefs; prevents unwanted problems. • Error messages from the XP are trapped and returned to the caller as SQL Server messages (display in the “Messages” window in Query Analyzer). • Error message output controlled dynamically by registry settings. See Item 3: softwareAB’s SQL Server XP Wrapper

9. General Tasks • Ensure that logging messages can be sent (aids troubleshooting). • Access/store parameters from caller. • Define the result set for the caller (where appropriate) • Set output parameters for the caller (where appropriate). • Ensure that an appropriate return code is registered with SQL Server

10. Logging Messages Back to SQL Server • SQL Server Messages are broken down by message type, message number, message class, message state, and line number. • softwareAB's XP always uses SRV_MSG_INFO for the message type and ABR_XP_MSGNUM_ERROR for the message number. • The message class is actually interpreted as “severity level” by SQL Server; softwareAB uses the following: 10 – Informational 14 – Warning 16 – Error 17 - Fatal • For the line number, softwareAB always uses __LINE__ from the C++ source file and the C++ source file name as the first part of the actual message text. • Use srv_sendmsg to send the message to SQL Server.

11. Storing Parameters from the calling SP • Use srv_rpcparams to get the number of parameters. • Store each parameter and its attributes using the following: srv_paramtype – returns one of SRVXXX (e.g. SRVVARCHAR). srv_parammaxlen – Max length of a parameter (such as 255 for a VARCHAR). srv_paramlen – Actual length of a paremeter. srv_paramdata – Raw data; must be typecast to the appropriate C++ type based on the parameter type. For NULL values, an actual NULL is passed to the C program by SQL Server. srv_paramstatus – Or against SRV_PARAMRETURN to determine if a parameter is OUTPUT or not. srv_paramname – Actual parameter name. Only passed if the calling SP provided the name. Typically, XPs use parameter ordering rather than parameter names. • softwareAB has a distinct class SqlServerParm to hold these values.

12. Define result set for caller The softwareAB SqlServer object makes it easy to define an output result set. Sample code: // define the ResultSet returned to the caller srv().rs_col_describe( "nRowNumber" , SqlServerCol::type_int , SqlServerCol::data_length( SqlServerCol::type_int ) ) ; srv().rs_col_describe( "sFileName" , SqlServerCol::type_string , 255 ) ; srv().rs_col_describe( "dCreated" , SqlServerCol::type_datetime , SqlServerCol::data_length( SqlServerCol::type_datetime ) ) ;

13. Add Items to a ResultSet Sample Code: SqlServerDatetime dtCreated( fileinfo.st_ctime ) ; SqlServerDatetime_rawPtr dtCreated_ptr = dtCreated.raw_ptr() ; srv().rs_col_setdata( 1, row ) ; srv().rs_col_setdata( 2, sFileName.c_str(), static_cast<int>(sFileName.size()) ) ; srv().rs_col_setdata( 3, *dtCreated_ptr ) ; srv().rs_send() ;

14. Feature Notes: ResultSet Processing • rs_col_setdata is overloaded for all the standard C++ types. • SQL Server datetime fields have special support for initialization from multiple time types. • Time cracking utilities are important both for performance and configuration reasons; see Item 4: Time Conversion in the attached packet. • Calling srv().rs_send() sends the next row of data back to SQL Server using the srv_sendrow API. Only one row of data may be returned at a time.

15. Set Output Variables • An XP call can have both output variables and a result set. • The softwareAB class SqlServerParm supports a set_output_data() method, which is overloaded for all necessary C++ types. • Internally, SqlServerParm uses the srv_paramset API to set the raw data bytes appropriately. • Once set, no other work is required by the XP.

16. Setting XP Return Code to SQL Server • Overall, SQL Server supports either an error or not an error through the srv_senddone API. The softwareAB SqlServer wrapper allows the caller to specify whether an error occurred or not. • Note: The first parameter to srv_senddone has a number of possible bit settings; be sure never to use SRV_DONE_FINAL for extended procs since SQL Server itself will send the SRV_DONE_FINAL on your behalf.

17. General Notes • XPs must use DBLIB (or some other client-side library such as ODBC) to issue queries back to SQL Server. The softwareAB SqlServer class provides functionality to issue either queries or commands and to process results. • Any connection made to SQL Server must differentiate between integrated security and SQL Server security. When using integrated security, the XP should use srv_impersonate_client to connect back to the named database as the connecting user who invoked the XP. • DBLIB client-side functions do *not* support VARCHAR > 255 chars, although SQL Server supports up to VARCHAR(4000).

18. Installing Extended Procs • They always install to the master database. • Execute permission must be explicitly granted to the roles you want allowed. • By default, the DLL is placed in the SQL Server Binn directory. • DLLs can’t be overwritten if they are in use; softwareAB works around this by naming each XP DLL based on the version number. The softwareAB install programs also check the version of the new DLL against the currently installed DLL and won’t install if the currently installed DLL has the same or greater version number. See Item 5: PHAMS Extended Proc Install Script

19. Invoking an Extended Procedure • All calls must be made specifying the master database. • Other than that, XP calls look exactly like SP calls. alter function uu_sp_encrypt( @sKey_in varchar(255) , @sData_in varchar(255) ) returns varchar(255) as begin declare @sResult varchar(255) if isnull(@sData_in, '') = '' set @sResult = '' else exec master.dbo.uu_xp_Crypt_Encrypt @sKey_in, @sData_in, @sResult output return @sResult end go