MS Sql Issues

Hi,

I get errors when i run the following prodcedure;
The error only occurs when i put in the command:
sp_cycle_agent_errorlog



ALTER procedure [dbo].[procpcmserrors] @alertvalue varchar(30) OUTPUT,
 @alertstring varchar(3000) OUTPUT as

DECLARE @SQL    VARCHAR(1000)
DECLARE @name   VARCHAR(5000)
DECLARE @sev    SMALLINT
 
BEGIN

SELECT @SQL = 'exec master..xp_readerrorlog '
INSERT INTO pcmserrors (entrytime, source, logEntry)
EXECUTE (@SQL)

DECLARE db_cursor CURSOR FOR
SELECT logentry, severity
FROM dbo.pcmserrors;

OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @name,@sev

WHILE @@FETCH_STATUS = 0  
BEGIN  
IF @name LIKE '%err%'
OR @name LIKE '%warn%'
OR @name LIKE '%cannot%'
OR @name LIKE '%could%'
OR @name LIKE '%fail%'
OR @name LIKE '%not%'
OR @name LIKE '%stop%'
OR @name LIKE '%terminate%'
OR @name LIKE '%bypass%'
OR @name LIKE '%roll%'
OR @name LIKE '%truncate%'
OR @name LIKE '%upgrade%'
OR @name LIKE '%victim%'
OR @name LIKE '%recover%'
OR @name LIKE '%IO requests taking longer than%'
AND @name NOT LIKE '%errorlog%'
AND @name NOT LIKE '%dbcc%'
AND @name NOT LIKE '%CHECKDB%'
UPDATE pcmserrors SET severity=1
WHERE logentry=@name
EXEC sp_cycle_errorlog
ELSE
UPDATE pcmserrors
SET severity=2
WHERE logentry=@name  
FETCH NEXT FROM db_cursor INTO @name, @sev  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT @alertvalue = severity, @alertstring=logentry
FROM dbo.pcmserrors
WHERE severity=1

DELETE FROM pcms.dbo.pcmserrors

END
LVL 1
hraja77Asked:
Who is Participating?
 
25112Connect With a Mentor Commented:
try this:
IF IS_SRVROLEMEMBER ('sysadmin') = 1
   print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
   print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
   print 'ERROR: The server role specified is not valid.'
http://msdn.microsoft.com/en-us/library/ms176015.aspx

Open in new window

0
 
makhanConnect With a Mentor Commented:
Hi,

Execute permissions for sp_cycle_errorlog are restricted to members of the sysadmin fixed server role.

Please check if you are a member of admin group.

HTH
0
 
hraja77Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.