INF: How to Create an Extended Stored Procedure to Do a Pager Notification
ID: Q171366
|
The information in this article applies to:
-
Microsoft SQL Server versions 6.0, 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.
The sample code provided above executes RasDial asynchronously. Hence, the
RasDial function returns immediately, before the connection is established,
and communicates its progress by means of a callback function, RasDialFunc.
If you want to use xp_paging synchronously, you only need to modify the
following line (remove RasDialFunc as well):
dwRet = RasDial( NULL, NULL, &rdParams, 0L, NULL, &hRasConn );
The xp_paging extended stored procedure takes one parameter, RAS address
entry. This extended stored procedure relies on RAS, and it is necessary to
set up RAS correctly for it to work properly.
Additional query words:
TAPI call automatic telephony application programming interface
Keywords : kbinterop kbnetwork kbprg SSrvInst SSrvProg SSrvWork
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbhowto kbinfo