2007. január 1., hétfő

Read MS-SQL Error Logs via SQL-DMO into TStrings

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;


Nincsenek megjegyzések:

Megjegyzés küldése