January 28, 2022 at 12:00 am
Comments posted to this topic are about the item SQL Server Monitoring Using Free Tools
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
January 28, 2022 at 11:52 am
I'd like to try SQLAgent but I get a conversion failed error. Can the author take a look a that?
January 28, 2022 at 1:33 pm
I'd like to try SQLAgent but I get a conversion failed error. Can the author take a look a that?
Same here. Two Agent jobs returned and then the script stops on the third one with the error message:
Msg 241, Level 16, State 1, Line 60
Conversion failed when converting date and/or time from character string.
January 28, 2022 at 1:56 pm
In my case the SQLAgent script chokes on a disabled job where the date and time values passed to the function msdb.dbo.agent_datetime(R.run_date, R.run_time) are null values.
January 28, 2022 at 3:56 pm
I will add a check to avoid the problem, but it does not happen for me on SQL Server 2012 SP4 or SQL Server 2019. The Microsoft function simply returns NULL when the parameters are NULL.
What version of SQL Server is running where the problem occurs?
You can replace...
, CONVERT(varchar(0040), msdb.dbo.agent_datetime(R.run_date, R.run_time), 120) AS next_run_date
, CONVERT(varchar(0040), msdb.dbo.agent_datetime(H.run_date, H.run_time), 120) AS last_run_date
with...
, CASE WHEN R.run_date IS NULL OR R.run_time IS NULL THEN NULL ELSE CONVERT(varchar(0040), msdb.dbo.agent_datetime(R.run_date, R.run_time), 120) END AS next_run_date
, CASE WHEN H.run_date IS NULL OR H.run_time IS NULL THEN NULL ELSE CONVERT(varchar(0040), msdb.dbo.agent_datetime(H.run_date, H.run_time), 120) END AS last_run_date
to fix it immediately.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
January 30, 2022 at 7:30 pm
I can make the msdb.dbo.agent_datetime function fail by supplying an invalid date value, such as 0 (zero). If you have invalid date values in your job history, and I'm not sure how that would happen, it will cause the SQLAgent tool to fail as described. The "fix" I suggested earlier will not do any good because the problem is not NULL values, it's invalid values, or so it appears.
Can you confirm the date values are invalid when the tool fails? The reported error message happens when the date value is 0 (zero).
It would not be pretty to properly check for invalid date values within the SQLAgent tool. It might be necessary to delete the history for the affected job(s). I will probably change the "fix" to...
, CASE WHEN R.run_date = 0 THEN NULL ELSE CONVERT(varchar(0040), msdb.dbo.agent_datetime(R.run_date, R.run_time), 120) END AS next_run_date
, CASE WHEN H.run_date = 0 THEN NULL ELSE CONVERT(varchar(0040), msdb.dbo.agent_datetime(H.run_date, H.run_time), 120) END AS last_run_date
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
February 1, 2022 at 8:02 am
I can make the msdb.dbo.agent_datetime function fail by supplying an invalid date value, such as 0 (zero). If you have invalid date values in your job history, and I'm not sure how that would happen, it will cause the SQLAgent tool to fail as described. The "fix" I suggested earlier will not do any good because the problem is not NULL values, it's invalid values, or so it appears.
Can you confirm the date values are invalid when the tool fails? The reported error message happens when the date value is 0 (zero).
Sorrry to have reported this incorrectly. Indeed it was a case where the run_date and the run_time values were both zero. Your latest fix have done the trick and the script now runs to completion.
I'm not sure why there are those zero-values. All I can say is that in my case both the Agent Job and the associated schedule is set as being Disabled where the script choked. So maybe that's why, but I have not investigated the the whys, the hows and the whens of this phenomenon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply