SQL Server How Can I get a error message with error code?

Question

LogDate                 ProcessInfo Text
...
2019-03-20 09:45:25.480 Logon       오류: 18456, 심각도: 14, 상태: 5.
2019-03-20 09:45:25.480 Logon       Login failed for user 'NE\NEO$'. 원인: 제공된 이름과 일치하는 로그인을 찾을 수 없습니다. [클라이언트: <local machine>]
2019-03-20 09:45:48.260 Logon       오류: 18456, 심각도: 14, 상태: 5.
2019-03-20 09:45:48.260 Logon       Login failed for user 'NE\NEO$'. 원인: 제공된 이름과 일치하는 로그인을 찾을 수 없습니다. [클라이언트: <local machine>]
...

When I execute sp_readerrorlog, I got these error messages.
(The error messages is really part of it in total error logs
and other ProcessInfo Value of error exists.)

I want to receive error messages related to error codes. ( It means two lines consisting of sets. And The error message what I want is not dependent on login.) My question is, How can I get error message with error code from query.

Try `sp_readerrorlog, 0, 1, N'오류:' `, however I'm not 100% I understand your problem — Java Devil, Mar 20 '19 at 02:19
I want the 'Login failed for user ...' message too. (Sorry.. I cant do english well.) — P.Lonnie, Mar 20 '19 at 02:42

score 1 · Accepted Answer · answered Mar 20 '19 at 07:17

if object_id('tempdb..#log') is not null drop table #log;

create table #log (id int identity primary key clustered, 
                   LogDate datetime,
                   ProcessInfo varchar(15),
                   txt varchar(8000));

insert into #log (LogDate, ProcessInfo, txt) exec xp_readerrorlog 0,1;

with cte as
(
select *,
       lead(txt) over (order by id) as txt1
from #log
)

select *
from cte
where txt like 'Error:%';

Here is the output example:

OMG. This is what I want. AWESOME. Thank you so much. – P.Lonnie Mar 20 '19 at 07:58 — P.Lonnie, Mar 20 '19 at 07:58

score 0 · Answer 2 · answered Mar 20 '19 at 03:07

To do it this way you would need to execute the stored procedure twice, once for each term you want to find.

You can combine these into a single query though with something like the following

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * FROM OPENROWSET('SQLNCLI', 
  'Server=   (local)\<instance_name>;Trusted_Connection=yes;',
  'exec sp_readerrorlog 0, 1, ''오류:'' WITH RESULT SETS 
  (( 
    EventTime nvarchar(25), 
    Login nvarchar(50), 
    Message nvarchar(4000)
  ))')
UNION
SELECT * FROM OPENROWSET('SQLNCLI', 
  'Server=(local)\<instance_name>;Trusted_Connection=yes;',
  'exec sp_readerrorlog 0, 1, ''Login failed for user'' WITH RESULT SETS 
  (( 
    EventTime nvarchar(25), 
    Login nvarchar(50), 
    Message nvarchar(4000)
  ))')

You will need to replace <instance_name> with your instance

Further details about this process can be found on this question.

Thank you. But the query catch only `Login failed for user` error. I want to receive other kinds of errors as well. — P.Lonnie, Mar 20 '19 at 06:05

SQL Server How Can I get a error message with error code?

2 Answers2