question

SQLLearning-0367 avatar image
0 Votes"
SQLLearning-0367 asked SQLLearning-0367 commented

A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 11, State 0, Line 0

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
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered SQLLearning-0367 commented

Not what they call a god user experience.

First about the error that you get. This basically means "internal error". That is, not your fault (at least not directly). This error can appear with severity 20 or 11. With 20 the accident happens in SQL Server, but you have severity 11 which means that the exception occurs in SSMS (or more precisely in SqlClient). I have found that in most cases this is due to SQL Server sending bad TDS. This is usually due to that something "unusual" happened.

Since this unusual is often an exception, I ran a trace with Error:Exception added, but I found no exception and that the procedure ran to the end.

But I have an experience of that INSERT-EXEC with extended stored procedures can be a bit unpolished. So I ran the call to xp_readerrorlog on its own, and voilà, now I got an error message:

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

So what are the correct data types? I took my chances, but nothing helped. I went to Google, and found https://www.mssqltips.com/sqlservertip/1476/how-to-read-log-file-in-sql-server-using-tsql/
and learnt that for later SQL Server versions, you need double quotes:

EXEC master.dbo.xp_readerrorlog 0, 1, Error, "18456", NULL, NULL, "desc"

Why this works, and not single quotes - I don't know. But it seems really crazy to me.

And, more sadly, I could get it to work with the @ErrNum variable, but it has a to be a constant, so if you want it to be a variable, you would need dynamic SQL.

Finally, I should point out that xp_readerrorlog is an undocumented stored procedure, and thus not supported for direct use.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for the quick response @ErlandSommarskog

EXEC master.dbo.xp_readerrorlog 0, 1, Error, "18456", NULL, NULL, "desc"

IIn the above command , i replaced single quotes with double quotes , but sill get the same error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Can you please help me with a working procedure

0 Votes 0 ·

Try changing the type of @ErrNum to nvarchar(50), or try this:

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

0 Votes 0 ·

Working from Viorel's post, I found that this worked for me

@ErrNum nvarchar (50)
...
      EXEC master.dbo.xp_readerrorlog 0, 1, N'Error', @ErrNum, NULL, NULL, N'desc'

That is, all string parameters needs to be nvarchar.

0 Votes 0 ·

Hi,
I i used dynamic SQL for the xp_readerrorlog and it worked.

0 Votes 0 ·