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.b19_1(<>fAnonymousType0`2 <>hTransparentIdentifier0) at System.Linq.Enumerable.SelectEnumerableIterator2.ToArray() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.OrderedEnumerable1.GetEnumerator()+MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.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.b0(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.