James Wiseman

When getting an unhelpful error deep in the bowels of some module developed in-house, it’s often desirable  to be able to insert some diagnostic information. There are a number of ways you can achieve this, for example, writing to a file, but one option is to (temporarily, at least) embed code that writes out to the event log.

In attempting to debug a bit of VB6 code, I found this invaluable. So here are some articles:

.NET http://support.microsoft.com/kb/301279

Pre- .NET http://support.microsoft.com/kb/154576

For VB5 and above, you can simply instantiate an object from the Logger class, and call LogEvent. Pre VB5, you’ll need the following class:

Option Explicit 

Private Declare Function RegisterEventSource Lib "advapi32" Alias _
  "RegisterEventSourceA" (ByVal lpUNCServerName As String, _
  ByVal lpSourceName As String) As Long
Private Declare Function DeregisterEventSource Lib "advapi32" _
  (ByVal hEventLog As Long) As Long
Private Declare Function ReportEvent Lib "advapi32" Alias _
  "ReportEventW" (ByVal hEventLog&, ByVal wType%, ByVal wCategory%, _
  ByVal dwEventID&, ByVal lpUserSid As Any, ByVal wNumStrings%, _
  ByVal dwDataSize&, plpStrings As Any, lpRawData As Any) As Boolean
Private Declare Function RegOpenKey Lib "advapi32" Alias "RegOpenKeyA" _
  (ByVal hKey As Long, ByVal lpSubKey As String, phkResult&) As Long
Private Declare Function RegCloseKey& Lib "advapi32" (ByVal hKey&)
Private Declare Function RegCreateKey Lib "advapi32" Alias _
  "RegCreateKeyA" (ByVal hKey&, ByVal lpSubKey$, phkResult&) As Long
Private Declare Function RegSetValueEx Lib "advapi32" Alias _
  "RegSetValueExA" (ByVal hKey&, ByVal lpValueName$, ByVal Reserved&, _
  ByVal dwType&, lpData As Any, ByVal cbData&) As Long
Private Const HKLM& = &H80000002
Public Enum LogTypes
  EVENTLOG_ERROR = 1
  EVENTLOG_WARNING = 2
  EVENTLOG_INFORMATION = 4
End Enum

Public Sub LogEvent(ByVal EvtString As String, LogType As LogTypes)
Dim hEventLog As Long, RegKey As String, EvtDll As String, hK&
Const EvPath$ = "System\CurrentControlSet\Services\Eventlog\Application\"
  hEventLog = RegisterEventSource(vbNullString, App.Title)
  If hEventLog = 0 Then Exit Sub
  RegKey = EvPath & App.Title
  EvtDll = "%SystemRoot%\System32\msvbvm60.dll"
  If RegOpenKey(HKLM, RegKey, hK) Then  'if not yet existent
    If RegCreateKey(HKLM, RegKey, hK) Then Exit Sub
    RegSetValueEx hK, "EventMessageFile", 0, 1, ByVal EvtDll, Len(EvtDll)
    RegSetValueEx hK, "TypesSupported", 0, 4, 4&, 4
    RegCloseKey hK
  Else
    RegCloseKey hK
  End If
  EvtString = Split(",Error:,Warning:,,Information:", ",")(LogType) _
              & vbCrLf & vbCrLf & EvtString
  ReportEvent hEventLog, LogType, 0, 1, 0&, 1, 0, StrPtr(EvtString), 0&
  DeregisterEventSource hEventLog
End Sub

A bit legacy, but I found it useful.

Programmers like new toys that they can play with, be it a second monitor, a new IDE, or a new programming language. They also like to feel that they are learning new things and not getting assigned to the technological scrap-heap and so will inherently favour (for example) C#3 above VB6 even when the final solution might be suboptimal.

I found myself drifting into this mindset recently when prototyping a design for component to fit in with an existing framework of COM components.

The component needed to call out to SQL Server 2005 to start an Agent Job. My first instinct was that I had to interface with SQL 2005 Native Client components through the .NET libraries. I envisaged something something like this:

//...get the connectionString somehow...
string connectionString = "Data Source=SOMESERVER;Initial Catalog=SomeDatabase;Integrated Security=SSPI;Provider=SQLNCLI.1;";
OleDbConnection oleDbConnection = new OleDbConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(oleDbConnection.DataSource);
Server oSqlServer = new Server(serverConnection);

JobServer oAgent = oSqlServer.JobServer;
Job oJob = oAgent.Jobs["Test"];
JobHistoryFilter oFilter = new JobHistoryFilter();
oFilter.JobName = "Test";
oJob.Start();

…or something similar therein.

The component would have to expose a COM Interface and be registered using REGASM, which would requrie manual intervension in the shipping process.

In addition to this, I would have neededed to organise for the SQL 2005 Client tools installed on four servers and undertake regression testing of the applications on these servers to inspire confidence that this hadn’t broken anything.

To be frank, it all seemed a little onerous and overcomplicated. Apart from simply wanting to use C#, the reason for this doing this was based on an assumption that I needed the .NET library code to hook into SQL Server and that there was no other way. Really? Have you ever heard such nonsense?

Without realising, I’d very nearly falling into the Second System Effect trap (of sorts). All I really had to do was make a call out to the SQL Server sp_start_job to achieve the same effect, and all I needed was trusty old MDAC 2.8. This meant I could write it in VB6 to fit in with existing framework components with none of the impact or complication inherent with the C#-developed component.

So, I set about writing a little test application as proof of concept. It looked something like this:

Private Sub Command1_Click()
    Dim connection As New ADODB.Connection
    Dim connectionString As String

    connectionString = "Data Source=SOMESERVER;Initial Catalog=SomeDatabase;Integrated Security=SSPI;Provider=SQLOLEDB.1;"

    Call connection.Open(connectionString)

    connection.Execute ("EXEC msdb.dbo.sp_start_job @job_name=N'Test'")

    MsgBox ("Success")
End Sub

But I really wanted to use C#! However,  in this instance it just wasn’t feasible.

So, in conclusion, don’t get blinded by shiny new toys or by a desire to improve oneself, don’t simply dive into the first solution that you come across, and remember, complication is ruination!