INF: How to Create Extended Stored Proc for a Pager Notification

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

This article describes some Win32 APIs related to Remote Access Service (RAS). The tasks described in this article are available as commands in Microsoft Visual C++ 4.2 or later versions for Windows. This article describes how to use RAS to page operators whenever server-down errors occur.

MORE INFORMATION

Sometimes, it is a good idea to let operators know when Microsoft SQL Server has severe errors. You can notify operators of these errors by setting the Alert Engines options. However, the pager notification option in the Alert Engines options must rely on the mailing system (such as Microsoft Mail or Microsoft Exchange Server). Even if you have a mail server, you will still need to have third-party vendor software that supports the dialing system through a gateway. The extended stored procedure below, written in Microsoft Visual C++, will notify the designated operator whenever unhandled SQL Server events occur. Please note that this code is only temporary solution to the pager notifications for an alert fail. If you want to have a robust paging system, you should set up the pager mailing address according to the Microsoft SQL Server documentation.

By using the extended stored procedure given below, it is possible to dial a pager number and leave error messages using RAS. This code also monitors the status of the modem connection. This verification can be controlled by using a RAS Win32 API call, RasGetConnectStatus. In this article, the customized API routines were written using Microsoft Visual C++ 5.0 to check the status of RAS connections by using a library-defined callback function (RasDialFunc) that the RasDial function calls when a change of state occurs during the RAS connection.

The sample program below illustrates the contents of the .dll file written in Microsoft Visual C++ 5.0 and shows a way to use the RAS connection successfully through the RAS Win32 SDK.

NOTE: This sample program illustrates many Microsoft Visual C++ commands. The use of these commands is beyond the scope of Microsoft SQL Server Technical Support. Users with substantial experience using API routines should be able to write the following sample .dll file. For this sample .dll file to work, you need a .def file to export the xp_paging function.

Sample C++ Program (DLL)

   // THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
   // ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
   // THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
   // PARTICULAR PURPOSE.

   #include <stdio.h>
   #include <windows.h>
   #include <ras.h>
   #include <srv.h>

   VOID WINAPI RasDialFunc(UINT unMsg, RASCONNSTATE rasconnstate, DWORD
   dwError);
   HRASCONN hRasConn;

   // To make the RasDial call:

   int xp_paging(SRV_PROC * srvproc)
   {
   LPRASCONN      lpRasConn = NULL;
         RASDIALPARAMS  rdParams;
         DWORD       cbBuf = 0;
         DWORD       cConn = 0;
         DWORD       dwRet = 0;

   // Set up RAS Dial parameters:
      rdParams.dwSize = sizeof(RASDIALPARAMS);

      lstrcpyn(rdParams.szEntryName, (const char
   *)srv_paramdata(srvproc,1),srv_paramlen(srvproc,1)+1);
   rdParams.szPhoneNumber[0] = '\0';
      rdParams.szCallbackNumber[0] = '*';
      rdParams.szCallbackNumber[0] = '\0';

      hRasConn = NULL;
      dwRet = RasDial( NULL, NULL, &rdParams, 0L,(RASDIALFUNC)RasDialFunc,
   &hRasConn );

      return dwRet;
   }

   // Purpose: Called by RasDial
   // Parameters:
   // unMsg - Type of RAS event that occurred
   // rasconnstate - Connection state about to be entered
   // dwError - Error code that may have occurred

   VOID WINAPI RasDialFunc(UINT unMsg, RASCONNSTATE rasconnstate, DWORD
   dwError)
   {
      if (dwError)
         RasHangUp(hRasConn);
   }

Sample DEF File

   LIBRARY   pager

   DESCRIPTION 'SQL Server Extended Stored Procedure - DLL'

   EXPORTS
          xp_paging

Sample Program (Notes)

• To create a .dll file in Visual C++, please refer to the Help file in Microsoft Visual C++ 4.0/5.0.
• Note that the Pager.dll should be in the current Mssql\Binn directory.
• In SQL Server, you can add this extended stored procedure by executing the following:

        sp_addextendedproc 'xp_paging', 'pager.dll'
  

• Set up RAS entry by adding an address entry name and the phone number. NOTE: Everything should be default.
• Type the following command to test the call;

        xp_paging 'RAS_address_entry_name'
  

• To compile the DLL properly, you need to include Opends60.lib, Ntwdblib.lib, and Rasapi32.lib in the project setting.

   dwRet = RasDial( NULL, NULL, &rdParams, 0L, NULL, &hRasConn );