Microsoft KB Archive/156100

= INF: Sample of SQL-DMO Connection Point Interface =

Article ID: 156100

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q156100



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.

It also contains a workaround to the Microsoft Knowledge Base article Q152621, "BUG: SQL-DMO: GetMemUsage Method Returns Empty String."

Before proceeding, make sure you take note of the following:


 * 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.



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: //


 * 1) include "afx.h"
 * 2) include "afxole.h"       //    OLE


 * 1) include "stdio.h"        //    Standard I/O header


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


 * 1) include "sqloleid.h"        //    SQL-DMO headers
 * 2) include "sqlole.h"


 * 1) include "ServerConnectionPoint.h" //    Server connection point implementation

// //  Defines: //
 * 1) define _SERVER        ""
 * 2) define _USER          "sa"
 * 3) define _PWD           ""
 * 4) 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 //


 * 1) include "windows.h"
 * 2) include "stdio.h"


 * 1) include "sqloleid.h"     //    SQL-DMO headers
 * 2) 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 KB156100

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.