Error executing extended stored procedure: Invalid Parameter Type

[fuzzyocelot]

Hi everyone!

I have an issue that's been bugging me for a little while now. The solution is probably something really simple, but I haven't been able to figure it out. I'm hoping someone could please shed some light on it for me.

In an SQL 2005 instance (on a Windows Server 2003 R2 Standard Edition), I've been trying to run the xp_readerrorlog extended stored procedure with parameters. Here's the statement I try to run:

master..xp_readerrorlog 1,'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG','error'

It works fine in 2000 but in 2005 I get the following error message:

Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

It will run fine if I just execute "master..xp_readerrorlog". That's also how I know I'm looking at the right errorlog. It says "Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG' " at the beginning of the results. I'd just like to be able to filter it on actual error messages.

I've searched this forum and tried Google but couldn't find a solution. I would really appreciate it if someone could please let me know what it is I'm doing wrong.

Thanks in advance!
Rebecca

 

[mrdenny]

According to SQL Profiler this command is used to display the current error log.

EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'

By my playing around I've figured out that the first paramater is the number of the log.
0 = ERRORLOG
1 = ERRORLOG.1
2 = ERRORLOG.2
etc

If you change the second paramater to a 2 you get the SQL Agent log. The First paramater works as above.
0 = SQLAGENT.OUT
1 = SQLAGENT.1
2 = SQLAGENT.2
etc

All filtering appears to be done in the GUI not in the XP.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

Thanks, mrdenny!

I tried it, and it worked! Woo hoo! I had to make a minor change, though, to get it to work for my purposes. See below:

EXEC master.dbo.xp_readerrorlog 0, 1, 'error', NULL, NULL, NULL

This filters the error log to show me only the entries where the description contains the word 'error'.

Thanks again for your help!
Rebecca