sp_readerrorlog vs xp_readerrorlog

Question: What is the difference between sp_readerrorlog and xp_readerrorlog?

Executive Summary Answer: ALMOST NOTHING

The More Detailed Answer: under the covers sp_readerrorlog is a stored procedure in the master database that checks that the user is part of the securityadmin group, then calls xp_readerrorlog, passing parameters if available.  Any differences between the two procedures does not affect the output, which means the information returned is the same.

When you run the following for sp_readerrlog:

sp_helptext N’sp_readerrorlog’
go

 The result returned is:

Text
————————————————–
create proc sys.sp_readerrorlog(
    @p1    int = 0,
    @p2    int = NULL,
    @p3    nvarchar(4000) = NULL,
    @p4    nvarchar(4000) = NULL)
as
begin
     if (not is_srvrolemember(N’securityadmin’) = 1)
    begin
       raiserror(15003,-1,-1, N’securityadmin’)
       return (1)
    end
       if (@p2 is NULL)
       exec sys.xp_readerrorlog @p1
    else
       exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end

 When you run the following for xp_readerrlog:

sp_helptext N’xp_readerrorlog’
go
 
The result returned is:
xp_readerrorlog

The xpstar.dll library file is a module that stores extended stored procedures for SQL Server.

 

 

2 Comments

  • Rick Bielawski says:
    12 August, 2014 at 1:45 pm

    There is a difference. xp_readerrorlog can’t take a varchar in the 3rd and 4th argument positions. Because it is a dll it does not support implicit casting and will only accept an nvarchar. sp_readerrorlog can implicitly convert a varchar to nvarchar so
    exec sp_readerrorlog 0,1,’text’
    works while
    exec xp_readerrorlog 0,1,’text’
    doesn’t. Only
    exec xp_readerrorlog 0,1,N’text’
    will work

    Reply
    • BlogAdmin says:
      17 August, 2014 at 12:55 pm

      Thanks Rick for that information. In testing, this is an issue in SQL 2012 & 2014. SQL 2005 & 2008R2 are not affected by this.

      Reply

Leave a Comment