Passing the cycle parameter into xp_readerrorlog

Question

Post reply

Passing the cycle parameter into xp_readerrorlog

Drew Salem

Ten Centuries

Points: 1060
More actions
August 17, 2006 at 2:25 am

#114609
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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

What is the exact error you're getting?

I couldn't try on a linked server, but it worked fine on my local box.

declare @sql varchar(255), @servername varchar(20)

set @servername = @@servername

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

exec (@sql)

/Kenneth

Drew Salem Ten Centuries Points: 1060 More actions · Answer 2

Bizzare. That works for me too. I wonder if it is a Linked Server thing.

The error I get is:

Msg 2812, Level 16, State 62, Procedure usp_GetErrorLog, Line 29

Could not find stored procedure 'master.dbo.xp_readerrorlog 2'.

Check out my code. Anything stand out?

set

ANSI_NULLS ON

set

QUOTED_IDENTIFIER ON

go

Create

procedure [dbo].[usp_GetErrorLog] @servername sysname

AS

SET

NOCOUNT ON

declare

@sql varchar(100)

--Read the error log of the servername provided

select

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

--Insert the contents of the error log into the table ServerErrorLog

INSERT

INTO ServerErrorLog

(ErrorLogText, ContinuationRow)

exec

@sql

UPDATE

ServerErrorLog

SET

Servername = @servername

WHERE

Servername

IS NULL

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 3

Strange it appears to see the 2 as part of the sp name rather than as a parameter

try this

Create procedure [dbo].[usp_GetErrorLog] @servername sysname

AS

SET NOCOUNT ON

declare @sql varchar(100), @p tinyint

set @p = 2

--Read the error log of the servername provided

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

--Insert the contents of the error log into the table ServerErrorLog

INSERT INTO ServerErrorLog

(ErrorLogText, ContinuationRow)

exec sp_executesql @sql, N'@p tinyint', @p

UPDATE

ServerErrorLog

SET

Servername = @servername

WHERE

Servername IS NULL

www.sql-library.com[/url]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

That is becuase errorlog.2 probably does not exist on the remote server!

What does EXEC servername.master..xp_enumerrorlogs tell you?

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

&nbsp

INSERT #Logs

EXEC (@ServerName + '.master.dbo.xp_enumerrorlogs')

CREATE TABLE #Log

(

Data VARCHAR(8000),

ContinuationRow INT

&nbsp

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

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

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

N 56°04'39.16"
E 12°55'05.25"

Drew Salem Ten Centuries Points: 1060 More actions · Answer 6

Hi Peter,

Thanks for replying (on both forums). I have double checked. I can browse the all 7 logs on the remote server. They are definately there. I do have the permissions.

Jules' solution looking promising. I didn't know about sp_executeSql.

The script you have provided returns the following error:

Syntax error converting the nvarchar value 'drewsserver.master.dbo.xp_readerrorlog ' to a column of data type tinyint.

So I tried this on its own:

--------------------

exec

sp_executesql

N'Execute DrewsServer.master.dbo.xp_readerrorlog @p',

N'@p smallint',

@p = 2

--------------------

DrewsServer is the name of the server (not a parameter). This DID return the required results. Notice that the @p is within the quotes.

So I modified your script to look like this:

--------------------

Alter

procedure [dbo].[usp_GetErrorLog] @servername sysname

AS

SET

NOCOUNT ON

declare

@sql varchar(100), @p tinyint

set

@p = 2

--Read the error log of the servername provided

select

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

--Insert the contents of the error log into the table ServerErrorLog

INSERT

INTO ServerErrorLog

(

ErrorLogText, ContinuationRow)

exec

sp_executesql @sql, N'@p tinyint', @p

UPDATE

ServerErrorLog

SET

Servername

= @servername

WHERE

Servername

IS NULL

------------------------

But it is now returning:

----------------------------------

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 15

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

----------------------------------

Any ideas? Is this because @p is a tinyint but it's put within @sql which is a varchar?:

-------------------------

declare @sql varchar(100), @p tinyint

set @p = 2

--Read the error log of the servername provided

select

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

--------------------------

I tried changing @p to a varchar but it returned the same error message. I knew it would fail, but I didn't think it would return the same error message.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Change DECLARE @sql VARCHAR(100) to

DECLARE @sql NVARCHAR(100).

That is one of the drawbacks for sp_executeSQL

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Strange with the error you say my code produces.

At least since I do not have either nvarchar not tinyint declarations...

N 56°04'39.16"
E 12°55'05.25"

Drew Salem Ten Centuries Points: 1060 More actions · Answer 9

Bingo!

Thanks Peter, thanks Jules.

Incidently, I did have to change

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

to

select

@sql = 'Execute ' + @servername + '.master.dbo.xp_readerrorlog @p'

Drew Salem Ten Centuries Points: 1060 More actions · Answer 10

Sorry, I was talking to both you and Jules (you first, then later refering to Jules code)