INF: Sample of SQL-DMO Connection Point Interface

• Microsoft SQL Server version 6.5

SUMMARY

The sample code in this article shows you how to:

• Create a connection point interface.



• Display detailed error information.



• Access Severity 0 to 10 error messages returned from SQL Server.

• You must implement all methods exposed by the Sink object. These are documented in the "What's New in SQL Server 6.5" portion of Books Online.



• You must appropriately handle the IUnknown interface methods. This sample uses the default implementations of the IUnknown interface where the last Release deletes the object.

MORE INFORMATION

MAIN.CPP

// 
//   The following code is an example of how to implement the SQL-DMO
//   Server connection point to trap all error messages returned from the
//   SQL Server.
// 
// 
//   Includes:
// 

#include "afx.h"
#include "afxole.h"        //    OLE

#include "stdio.h"         //    Standard I/O header

#include "initguid.h"         //    Only included once to define the GUID and UUID's properly

#include "sqloleid.h"         //    SQL-DMO headers
#include "sqlole.h"

#include "ServerConnectionPoint.h"  //    Server connection point implementation

// 
//   Defines:
// 
#define  _SERVER        ""
#define  _USER          "sa"
#define _PWD            ""
#define  _BAD_COOKIE 99999

// 
//   Function declarations:
// 

BOOL  bInitialize(void);

void  vUninitialize(void);

HRESULT hrDisplayError(HRESULT hRes);
BOOL  bInstallConnectionPointHandler(void);

void  vDoSomeStuff(void);

// 
//   Local variables:
// 

BOOL           bCoInit     =  FALSE;
LPSQLOLESERVER    iSQLServer  =  NULL;
LPCONNECTIONPOINT    iCP      =  NULL;
CSQLServerSink *  pServerSink =  NULL;
DWORD       dwCookie =  _BAD_COOKIE;

// 
//   MAIN ROUTINE
//   ------------
void main(void)
{
   if(bInitialize())
   {
      if(bInstallConnectionPointHandler())
      {
         vDoSomeStuff();
      }
   }

   // 
   //    Perform cleanup operations:
   // 
   vUninitialize();
   printf("\n\nSample run completed...\n\n");
}

// 
//   Initialize - perform all init operations:
// 

BOOL bInitialize(void)
{
   BOOL  bRC   =  FALSE;

   printf("\n...Initializing OLE...");
   if(SUCCEEDED(CoInitialize(NULL)))
   {
      bCoInit = TRUE;

      // 
      //    Create a Server object:
      // 
      printf("\n...Creating SQL Server Object...");
      if(SUCCEEDED(hrDisplayError(CoCreateInstance(CLSID_SQLOLEServer,
                        NULL,
                        CLSCTX_INPROC_SERVER,
                        IID_ISQLOLEServer,
                        (LPVOID *)&iSQLServer))))
      {
         bRC = TRUE;
      }
   }
   else
   {
      printf("\nCoInitialize failed.");
      bRC = bCoInit = FALSE;
   }
   return bRC;
 }

// 
//    Uninitialize - perform all shutdown operations:
// 
void vUninitialize(void)
{
   printf("\n...Performing the Unadvice...");
   if((dwCookie != _BAD_COOKIE) && (iCP))
      iCP->Unadvise(dwCookie);

   printf("\n...Cleaning up object memory...");

   // 
   //    Not part of above sequence in case Advise failed and dwCookie was
   //   (0)
   // 
   if(iCP)
      iCP->Release();

   // 
   //    Clean up the server object:
   // 
   if(iSQLServer)
      iSQLServer->Release();

   // 
   //    Shut down OLE:
   // 
   if(bCoInit)
      CoUninitialize();
}

// 
//    Install the connection point handler:
// 

BOOL bInstallConnectionPointHandler(void)
{
   BOOL                 bRC      =  FALSE;
   LPCONNECTIONPOINTCONTAINER       iCPContainer   =  NULL;

   // 
   //    Create an instance of the Server Sink object:
   // 
   pServerSink = new CSQLServerSink;

   if(pServerSink)
   {
      printf("\n...Creating Connection Point Container...");
      if(SUCCEEDED(hrDisplayError(iSQLServer->QueryInterface(IID_IConnectionPointContainer,
            (LPVOID *) &iCPContainer))))
      {
         printf("\n...Finding the SQL Server Sink connection point...");
         if(SUCCEEDED(hrDisplayError(iCPContainer->FindConnectionPoint
             (IID_ISQLOLEServerSink, &iCP))))
         {
            printf("\n...Advising connection point that we are available to
               receive events...");
            if(SUCCEEDED(hrDisplayError(iCP->Advise(pServerSink, &dwCookie))))
            {
               bRC = TRUE;
            }
            else
               dwCookie = _BAD_COOKIE;
         }
         iCPContainer->Release();
      }
   }
   else
   {
      printf("\nAttempt to create server sink object failed due to a memory error.");
   }

   // 
   //    Free memory if necessary:
   // 
   if((pServerSink) && (FALSE == bRC))
   {
      delete pServerSink;
      pServerSink = NULL;
   }
   return bRC;
}

// 
//    Do some things to cause the Sink events to fire:
// 
void vDoSomeStuff(void)
{
   printf("\n...Setting connection options...");
   if(SUCCEEDED(hrDisplayError(iSQLServer->SetLoginTimeout(5))))
   {
      if(SUCCEEDED(hrDisplayError(iSQLServer->SetApplicationName("Server Sink"))))
      {
         if(SUCCEEDED(hrDisplayError(iSQLServer->SetHostName("SQL-DMO"))))
         {
            if(SUCCEEDED(hrDisplayError(iSQLServer->SetNetPacketSize(4096))))
            {
               printf("\n...Attempting to connect...");
               if(SUCCEEDED(hrDisplayError(iSQLServer->Connect(_SERVER,
                      _USER, _PWD))))
               {
                  // 
                  //    Cause a meesage to be fired from SQL Server.
                  // 
                  //    This is one workaround for Q152621 (Bug #15500.)
                  //    You could also use ExecuteWithResultsAndMessages.
                  // 
                  //    You may want to use Sink to capute information
                  //    output from any DBCC command because the standard
                  //    implementation of Database.CheckAllocations uses
                  //    'WITH NO_INFOMSGS'
                  // 
                  hrDisplayError(iSQLServer->ExecuteImmediate("dbcc memusage"));

                  // 
                  //    This will fire the Sink event.
                  // 
                  hrDisplayError(iSQLServer->ExecuteImmediate("raiserror(1204, 1, 1)"));
                  // 
                  //    This will NOT fire the Sink event due to Sev level.
                  // 
                  hrDisplayError(iSQLServer->ExecuteImmediate("raiserror(1204, 11, 1)"));
                  // 
                  //    Close connection.
                  // 
                  hrDisplayError(iSQLServer->DisConnect());
               }
            }
         }
      }
   }
}

// 
//    Display Error Information as a result of an OLE call
// 
HRESULT hrDisplayError(HRESULT hRes)
{
   LPERRORINFO    lpErrorInfo    =  NULL;
   BSTR        bstrDesc;
   BSTR        bstrSource;

   if(FAILED(hRes))
   {
      if(SUCCEEDED(GetErrorInfo(0, &lpErrorInfo)))
      {
         lpErrorInfo->GetDescription(&bstrDesc);
         lpErrorInfo->GetSource(&bstrSource);
         printf("\n\nhrDisplayError: %S\n%S\n", bstrSource, bstrDesc);
         lpErrorInfo->Release();
         SysFreeString(bstrDesc);
         SysFreeString(bstrSource);
      }
      else
         printf("\n\nUnable to obtain detailed error information.");
   }
   return hRes;
} 

ServerConnectionPoint.h

// 
//    SQL-DMO Server Connection Point Interface Declaration and
//    Implementation
// 

#include "windows.h"
#include "stdio.h"

#include "sqloleid.h"      //    SQL-DMO headers
#include "sqlole.h"

class CSQLServerSink : public ISQLOLEServerSink
{
   public:
      CSQLServerSink()
         {
            m_uiRefCount   =  0;
         }
      ~CSQLServerSink()
         {
         }

      // 
      // IUnknown Interface
      // 

      STDMETHOD(QueryInterface) (THIS_ REFIID riid, LPVOID *ppvObj)
         {
            if((riid == IID_IUnknown) || (riid == IID_IASQLOLEServerSink))
            {
               AddRef();
               *ppvObj = this;

               return NOERROR;
            }
            else
               return E_NOINTERFACE;
         }
      STDMETHOD_(ULONG,AddRef) (THIS)
         {
            return ++m_uiRefCount;
         }
      STDMETHOD_(ULONG,Release) (THIS)
         {
            --m_uiRefCount;

            if(0 == m_uiRefCount)
                  delete this;

            return m_uiRefCount;
         }

      // 
      // Sink properties and methods:
      // 
      STDMETHOD(QueryTimeout)(THIS_ SQLOLE_LPCSTR strMessage, LPBOOL pbContinue)
         {
            printf("\nSINK QueryTimeout - %s", strMessage);
            *pbContinue = FALSE;
            return NOERROR;
         }

      // 
      //    Only for designated error messages Sev 10 or less.
      // 
      STDMETHOD(ServerMessage)(THIS_ long lMessageSeverity, long lMessageNumber,
                  long lMessageState, SQLOLE_LPCSTR strMessage)
         {
            printf(  "\nSINK ServerMessage"
                  "\nError: %ld"
                  "\nSev:   %ld"
                  "\nState: %ld"
                  "\n%s\n",
                     lMessageNumber, lMessageSeverity, lMessageState, strMessage);
            return NOERROR;
         }

      STDMETHOD(ConnectionBroken)(THIS_ SQLOLE_LPCSTR strMessage, LPBOOL pbRetry)
         {
            printf("\nSINK ConnectionBroken - %s", strMessage);
            *pbRetry = TRUE;
            return NOERROR;
         }

      STDMETHOD(RemoteLoginFailed)(THIS_ long lMessageSeverity, long lMessageNumber,
                     long lMessageState, SQLOLE_LPCSTR strMessage)
         {
            printf(  "\nSINK RemoteLoginFailed"
                  "\nError: %ld"
                  "\nSev:   %ld"
                  "\nState: %ld"
                  "\n%s\n",
                     lMessageNumber, lMessageSeverity, lMessageState, strMessage);
            return NOERROR;
         }
      // 
      //    Shows actual commands begin sent from SQL-DMO to SQL Server.
      // 
      STDMETHOD(CommandSent)(THIS_ SQLOLE_LPCSTR strSQL)
         {
            printf("\nSINK CommandSent - %s", strSQL);
            return NOERROR;
         }

   private:

      UINT  m_uiRefCount;
}; 

Keywords : kbnetwork SSrvDMO
Version : 6.5
Platform : WINDOWS
Issue type :