Get-SqlErrorLog: Unable to cast object of type 'System.DBNull' to type 'System.DateTime'.

[webfly2]

When executing Get-SqlErrorLog -ServerInstance "ServerName" I am getting the following Error:

Get-SqlErrorLog: Unable to cast object of type 'System.DBNull' to type 'System.DateTime'.

Verbose and Debug Output:

DEBUG: [Get-SqlErrorLog] In method ProcessRecord. Resolving targets.
DEBUG: [Get-SqlErrorLog] Resolving targets: Parameter Set = 'ByName'
DEBUG: [Get-SqlErrorLog] Connecting to server ServerName with Windows Authentication.
DEBUG: [Get-SqlErrorLog] Attempting to connect to server...
DEBUG: [Get-SqlErrorLog] Connection succeeded.
DEBUG: [Get-SqlErrorLog] Resolved target [ServerName]
DEBUG: [Get-SqlErrorLog] GetShouldProcessTargetString in SqlCmdlet.
DEBUG: [Get-SqlErrorLog] Validating Target
DEBUG: [Get-SqlErrorLog] ValidateTarget in SqlCmdlet. Returning true.
DEBUG: [Get-SqlErrorLog] Target is valid. Calling BeginTargetProcessing.
DEBUG: [Get-SqlErrorLog] In BeginTargetProcessing
DEBUG: [Get-SqlErrorLog] Subscribing to server events: InfoMessage, StatementExecuted
DEBUG: [Get-SqlErrorLog] Done with BeginTargetProcessing. Calling ProcessTarget.
VERBOSE: [Get-SqlErrorLog] select SERVERPROPERTY(N'servername')
VERBOSE: [Get-SqlErrorLog]
create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(44), Size int)

        insert #err_log_tmp exec master.dbo.sp_enumerrorlogs

SELECT
'Server[@name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/ErrorLog[@archiveno=''' + CAST(er.ArchiveNo AS sysname) + ''']' AS [Urn],
CAST(er.ArchiveNo AS sysname) AS [Name],
er.ArchiveNo AS [ArchiveNo],
CASE WHEN ISDATE(er.CreateDate) = 1 THEN CONVERT(datetime, er.CreateDate, 101) ELSE NULL END AS [CreateDate],
er.Size AS [Size]
FROM
#err_log_tmp er

        drop table #err_log_tmp

DEBUG: [Get-SqlErrorLog] GetLogs: srv='ServerName'
DEBUG: [Get-SqlErrorLog] GetLogs: afterDate='07/01/2024 00:00:00'
DEBUG: [Get-SqlErrorLog] GetLogs: beforeDate=07/01/2024 15:41:05
DEBUG: [Get-SqlErrorLog] GetLogs: ascending=False
DEBUG: [Get-SqlErrorLog] Exception occurred System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.DateTime'.
at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.<>c.b__19_1(<>f__AnonymousType02 <>h__TransparentIdentifier0) at System.Linq.Enumerable.SelectEnumerableIterator2.ToArray()
at System.Linq.Buffer1..ctor(IEnumerable1 source)
at System.Linq.OrderedEnumerable1.GetEnumerator()+MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator2.ToList()
at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.GetLogs(Server srv, DateTime afterDate, DateTime beforeDate, Boolean ascending)
at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.<>c__DisplayClass18_0.b__0(DateTime afterDate, DateTime beforeDate)
at Microsoft.SqlServer.Management.PowerShell.Shared.CmdletHelper.ExecuteAfterHandlingStartTimeEndTimeSinceAndTimeSpan(Action2 execute, Nullable1 startTime, Nullable1 endTime, Nullable1 since, Nullable1 timeSpan) at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.ProcessTarget(Server target, SmoRecordContext context) at Microsoft.SqlServer.Management.PowerShell.SmoContextSensitiveTargetedCmdlet1.ProcessTarget(SmoRecordContext target)
at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord()
DEBUG: [Get-SqlErrorLog] Calling EndProcessing.
DEBUG: [Get-SqlErrorLog] In EndTargetProcessing
DEBUG: [Get-SqlErrorLog] Unsubscribing from server events: InfoMesage, StatementExecuted
Get-SqlErrorLog: Unable to cast object of type 'System.DBNull' to type 'System.DateTime'.

I tried this on different SQL Server Hosts with the same output.

The output of $PSVersionTable:

Name Value

---

PSVersion 7.4.3
PSEdition Core
GitCommitId 7.4.3
OS Microsoft Windows 10.0.20348
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

The output of (Get-Module SQLServer).Version:

Major Minor Build Revision

---

22 2 0 -1

The version of Sql Server or Azure SQL: SQL Server Version 16.0.1115

[Matteo-T]

Looks like an unexpected NULL ended up in the table/view/DMV... and the logic I have to fetch and process the returned result set is tripping over it. I've never observed it...

Just out of curiosity, what do you see if you go straight to T-SQL... and browse around? Or maybe even useing SSMS to see the logs?

[Matteo-T]

Hi @webfly2, from what I can guess this could be an issue in SMO.

Is it possible your SQL Server is configured for a language other than English? I can see that T-SQL fragment (generated by SMO) to return NULL for the CreateDate column when:

1. The language (of my connection) is SET LANGUAGE Italian
2. My log files happen to be created on a day of the month that is >12

I'll log a bug against SMO while I see what I can do to workaround it...

[Matteo-T]

I've applied a workaround and I expect the fix to rollout today (?) with v22.3.