Cycling through xp_readerrorlog

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cycling through xp_readerrorlog

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-08-16 : 12:15:22

Hi

The following is a line out of a script that populates a table with log info, where @servername is a linked server:

select @sql = @servername + '.master.dbo.xp_readerrorlog'

[xp_readerrorlog (int)] retrieves log data where (int) is the cycle number. So [xp_readerrorlog 2] would bring back the second cycle.

SQL Server will not except the int parameter as:

select @sql = @servername + '.master.dbo.xp_readerrorlog 2'

It thinks 'xp_readerrorlog 2' is the name of the stored procedure. How can I pass this paramter into the line?

Thanks

Drew

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-08-17 : 04:05:47

Anyone?

Thanks

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 04:26:23

Works fine for me, up to 6.
Logs 1 to 6 works well, and then 7 fails.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 04:28:23

Xp_readerrorlog will show you a given SQL Server error log and is useful when you want to quickly debug an issue via Query Analyzer without having to open the slower Enterprise Manager. It is also possible to loop through the error log in ADO using xp_readerrorlog to look for keywords like "ERROR" or "LOGIN FAILED" and act upon those accordingly.

To execute the stored procedure, you can run it without any parameters as shown below:

master..xp_readerrorlog

or you can call a given error log from the archive (like ERRORLOG.5) by typing the error log that you wish to see after the stored procedure as shown below:

master..xp_readerrorlog 5

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 04:29:37

Also look at this SP

EXEC master..xp_enumerrorlogs

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-08-17 : 04:48:55

Hi Peso,

Thanks for responding.

"xp_readerrorlog (int)" works for me too.
"myLinkedServer.master.dbo.xp_readerrorlog (int)" also works for me.

But I am populating a table with the results of these logs, which works fine for the first log i.e.

select @sql = @servername + '.master.dbo.xp_readerrorlog'

INSERT INTO ServerErrorLog
exec @sql

But doesn't work for subsequent logs i.e with the (int)

select @sql = @servername + '.master.dbo.xp_readerrorlog 2'
INSERT INTO ServerErrorLog
exec @sql

It thinks "xp_readerrorlog 2'" is the name of the stored procedure.

I need to tell it that the "2" is a parameter, not part of the name.

Incidently, EXEC master..xp_enumerrorlogs is good. I didn't know about that one. I can use that for something else!

Thanks

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:46:11

That is becuase errorlog.2 probably does not exist on the remote server!
What does EXEC servername.master..xp_enumerrorlogs tell you?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:58:43

This is probably what you want.

DECLARE	@SQL VARCHAR(8000),
	@ServerName VARCHAR(100)

SELECT	@ServerName = 'anysqlservermachine'

CREATE TABLE	#Logs
		(
			Archive INT,
			dt DATETIME,
			FileSize INT
		)

INSERT	#Logs
EXEC	(@ServerName + '.master.dbo.xp_enumerrorlogs')

CREATE TABLE	#Log
		(
			Data VARCHAR(8000),
			ContinuationRow INT
		)

DECLARE	@CurrentLog INT,
	@MaxLog INT

SELECT	@CurrentLog = MIN(Archive),
	@MaxLog = MAX(Archive)
FROM	#Logs

WHILE @CurrentLog <= @MaxLog
	BEGIN
		SELECT	@SQL = @ServerName + '.master.dbo.xp_readerrorlog ' + CASE WHEN @CurrentLog = 0 THEN '' ELSE CONVERT(VARCHAR, @CurrentLog) END

		INSERT	#Log
		EXEC	(@SQL)

		SELECT	@CurrentLog = MIN(Archive)
		FROM	#Logs
		WHERE	Archive > @CurrentLog
	END

DROP TABLE	#Logs

SELECT * FROM #Log

DROP TABLE #Log

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 06:06:03

Also, do you have appropriate rights on the remote server to read the log files?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources