Read MS-SQL Error Logs via SQL-DMO into TStrings (Views: 717)
Problem/Question/Abstract: Functions to load a StringList with MS-SQL Server Error Logs via SQL-DMO. MS-SQL DMO is a COM/OLE object that can do many things, in this article we just read the error logs off the server. There a two overloaded functions, one for Windows Authentication, and another for SQL Authentication. The function returns true if successful. The default log number is 0 (Current Log). // Windows Authentication function SqlErrorLog(AStrings: TStrings; const ASqlServer: string; ALogNumber: integer = 0): boolean; overload; // SQL Authentication function SqlErrorLog(AStrings: TStrings; const ASqlServer, AUserName, APassword: string; ALogNumber: integer = 0): boolean; overload; Example // Load memo using Default Log 0 and Windows Authentication if SqlErrorLog(Memo1.Lines, 'BusServer1') then ..... // Load memo using Log 3 and SQL Authentication if SqlErrorLog(Memo1.Lines, 'BusServer', 'harry', 'mypass', 3) then .. Answer: uses ComObj, Variants; {Variants is for Delphi 7} // ===================================================== // PRIMITIVE Load MS SQL Server Error Log Function // ===================================================== function _SqlErrorLog(AObject: OleVariant; AStrings: TStrings; const ASqlServer: string; ALogNumber: integer): boolean; var oLog: OleVariant; bResult: boolean; i: integer; begin try AObject.Connect(ASqlServer); try AStrings.BeginUpdate; oLog := AObject.ReadErrorLog(ALogNumber); for i := 1 to oLog.Rows do AStrings.Add(oLog.GetColumnString(i, 1)); oLog := Unassigned; finally AStrings.EndUpdate; end; AObject.Disconnect; bResult := true; except bResult := false; end; AObject := Unassigned; Result := bResult; end; // ===================================================== // Get SQL Server Log using Windows Authentication // ===================================================== function SqlErrorLog(AStrings: TStrings; const ASqlServer: string; ALogNumber: integer = 0): boolean; overload; var oDMO: OleVariant; bResult: boolean; begin AStrings.Clear; try oDMO := CreateOleObject('SQLDMO.SQLServer'); oDMO.LoginSecure := true; bResult := _SqlErrorLog(oDMO, AStrings, ASqlServer, ALogNumber); except bResult := false; end; Result := bResult; end; // ===================================================== // Get SQL Server Log using SQL Authentication // ===================================================== function SqlErrorLog(AStrings: TStrings; const ASqlServer, AUserName, APassword: string; ALogNumber: integer = 0): boolean; overload; var oDMO: OleVariant; bResult: boolean; begin AStrings.Clear; try oDMO := CreateOleObject('SQLDMO.SQLServer'); oDMO.LoginSecure := false; oDMO.Login := AUserName; oDMO.Password := APassword; bResult := _SqlErrorLog(oDMO, AStrings, ASqlServer, ALogNumber); except bResult := false; end; Result := bResult; end; |