On my SQL 2017 Server, i created the below stored procedure to read error logs in SQL. The Stored proc gets created successfully, but when i execute the proc, it throws me this error.
Can anyone please help.
Basically i am trying to create a stored procedure , which will fetch the error details, within the recent 10 mins, when we pass the error number. But somehow it seems to be failing giving the error mentioned in subject , when i execute my proc. i execute this procedure on a newly created SQL database.
Track_ErrorLog @ErrNum='18456'. Below is my proc code.
-- CHECK FOR latest entries of SQL Error 18456
IF OBJECT_ID(N'Track_ErrorLog', N'P') IS NOT NULL
DROP PROCEDURE Track_ErrorLog
GO
CREATE PROCEDURE Track_ErrorLog (@ErrNum varchar (50))
AS
BEGIN
Declare @Err nvarchar(50)
SET @Err=@ErrNum
SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#ErrorLg', N'U') IS NOT NULL
DROP TABLE #ErrorLg
IF OBJECT_ID(N'tempdb..#ControlReadErrorLog', N'U') IS NOT NULL
DROP TABLE #ControlReadErrorLog
--SELECT * FROM #ControlReadErrorLog
CREATE TABLE #ErrorLg
(
LogDate DATETIME,
ProcessInfo VARCHAR(255),
[Text] VARCHAR(MAX)
)
INSERT INTO #ErrorLg
EXEC master.dbo.xp_readerrorlog 0, 1, N'Error', @ErrNum, NULL, NULL, N'desc'
DECLARE @statement varchar(255),@Text varchar(255),@LogDate datetime,@sql varchar(MAX);
SELECT TOP 1 LogDate,ProcessInfo,[Text] INTO #ControlReadErrorLog
FROM #ErrorLg where LogDate >= dateadd(minute,-10,GETDATE()) --GET last 10 min records
order by LogDate desc
SELECT @Text =[Text],@LogDate=LogDate FROM #ControlReadErrorLog
--check count greater than 0, then log to event viewer
IF(@@ROWCOUNT >0)
BEGIN
SET @statement = CONCAT('error ',@Err,' occured at time ',@LogDate,' . Error details --> ',@Text,' .Please check further.')
SELECT @statement
EXEC master..xp_logevent 65007,@statement, INFORMATIONAL
END
DELETE #ErrorLg
DELETE #ControlReadErrorLog
DROP TABLE #ControlReadErrorLog
DROP TABLE #ErrorLg
END