Mega Code Archive
Read MS SQL Error Logs via SQL DMO into TStrings
<![CDATA[
Title: Read MS-SQL Error Logs via SQL-DMO into TStrings
Question: 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;
]]>