Favorable
asked on
SQL Server Script to Import ErrorLog
Not very good with script.
Can somebody modify the following script, so it can be executed in SQL Server 2008 and R2 without returning error. The was tested on a 2000 Instance of SQL but never tested on 2K5 or 2K8.
CREATE PROC sp_import_errorlog
(
@log_name sysname,
@log_number int = 0,
@overwrite bit = 0
)
AS
/************************* ********** ********** ********** ********** ********** ********** ********** **
Purpose: To import the SQL Server error log into a table, so that it can be queried
Tested on: SQL Server 2000
Limitation: With error messages spanning more than one line only the first line is included in the table
Example 1: To import the current error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog'
Example 2: To import the current error log to table myerrorlog, and overwrite the table
'myerrorlog' if it already exists
EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1
Example 3: To import the previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 1
Example 4: To import the second previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 2
************************** ********** ********** ********** ********** ********** ********** ********** */
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log
IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
BEGIN
IF @overwrite = 0
BEGIN
RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
RETURN -1
END
ELSE
BEGIN
EXEC('DROP TABLE ' + @log_name)
END
END
--Temp table to hold the output of sp_readerrorlog
CREATE TABLE #errlog
(
err varchar(1000),
controw tinyint
)
--Populating the temp table using sp_readerrorlog
INSERT #errlog
EXEC sp_readerrorlog @log_number
--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #errlog
SET ROWCOUNT 0
SET @sql = 'SELECT
CONVERT(DATETIME,LEFT(err, 23)) [Date],
SUBSTRING(err,24,10) [spid],
RIGHT(err,LEN(err) - 33) [Message],
controw
INTO ' + QUOTENAME(@log_name) +
' FROM #errlog ' +
'WHERE controw = 0'
--Creates the table with the columns Date, spid, message and controw
EXEC (@sql)
--Dropping the temporary table
DROP TABLE #errlog
Can somebody modify the following script, so it can be executed in SQL Server 2008 and R2 without returning error. The was tested on a 2000 Instance of SQL but never tested on 2K5 or 2K8.
CREATE PROC sp_import_errorlog
(
@log_name sysname,
@log_number int = 0,
@overwrite bit = 0
)
AS
/*************************
Purpose: To import the SQL Server error log into a table, so that it can be queried
Tested on: SQL Server 2000
Limitation: With error messages spanning more than one line only the first line is included in the table
Example 1: To import the current error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog'
Example 2: To import the current error log to table myerrorlog, and overwrite the table
'myerrorlog' if it already exists
EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1
Example 3: To import the previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 1
Example 4: To import the second previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 2
**************************
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log
IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
BEGIN
IF @overwrite = 0
BEGIN
RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
RETURN -1
END
ELSE
BEGIN
EXEC('DROP TABLE ' + @log_name)
END
END
--Temp table to hold the output of sp_readerrorlog
CREATE TABLE #errlog
(
err varchar(1000),
controw tinyint
)
--Populating the temp table using sp_readerrorlog
INSERT #errlog
EXEC sp_readerrorlog @log_number
--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #errlog
SET ROWCOUNT 0
SET @sql = 'SELECT
CONVERT(DATETIME,LEFT(err,
SUBSTRING(err,24,10) [spid],
RIGHT(err,LEN(err) - 33) [Message],
controw
INTO ' + QUOTENAME(@log_name) +
' FROM #errlog ' +
'WHERE controw = 0'
--Creates the table with the columns Date, spid, message and controw
EXEC (@sql)
--Dropping the temporary table
DROP TABLE #errlog
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER