Passing the cycle parameter into xp_readerrorlog
-
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
-
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
-
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
-
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
-
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
 
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
N 56°04'39.16"
E 12°55'05.25" -
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" -
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.
-
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" -
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" -
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'
-
Sorry, I was talking to both you and Jules (you first, then later refering to Jules code)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply