IF OBJECT_ID(N 'tempdb..##err') IS NOT NULL BEGIN
DROP
TABLE ##err
END CREATE TABLE ##err (
[LogDate] DATETIME2,
[ProcessInfo] VARCHAR(255),
[Text] VARCHAR(max)
) INSERT INTO ##err
EXEC sp_readerrorlog
SELECT
*,
CASE WHEN ISNUMERIC(
LTRIM(
RTRIM(
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
CHARINDEX(
' seconds',
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
9999
),
0
)
)
)
)
) = 1 THEN CAST(
LTRIM(
RTRIM(
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
CHARINDEX(
' seconds',
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
9999
),
0
)
)
)
) AS INT
) ELSE NULL END AS TimeLeftinSeconds,
CASE WHEN ISNUMERIC(
LTRIM(
RTRIM(
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
CHARINDEX(
' seconds',
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
9999
),
0
)
)
)
)
) = 1 THEN CAST(
LTRIM(
RTRIM(
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
CHARINDEX(
' seconds',
SUBSTRING(
TEXT,
CHARINDEX('approximately ', TEXT, 0) + 14,
9999
),
0
)
)
)
) AS FLOAT
) / 60 ELSE NULL END AS TimeLeftinMinutes
FROM
##err
ORDER BY
Logdate DESC IF OBJECT_ID(N 'tempdb..##err') IS NOT NULL BEGIN
DROP
TABLE ##err
END
Facebook Social Comments