0

I have had an agent job running sp_AskBrent for some time, running with the following parameters:

EXEC sp_AskBrent @Expertmode=1
,@OutputDatabaseName = 'DBName'
, @OutputSchemaName = 'dbo'
, @OutputTableName = 'AskBrentResults'
,@seconds = 30
, @OutputTableNameFileStats='AskBrentResultsFileStats'
,@OutputTableNamePerfmonStats='AskBrentResultsPerfmonStats'
, @OutputTableNameWaitStats='AskBrentResultsWaitStats'

Today I wanted to update to the new renamed procedure sp_BlitzFirst. After creating the new procedure, I changed the code in the agent job to run sp_BlitzFirst in stead, with the same parameters. Everything seems to go OK until it is time to output values to the tables, then there seem to be some kind of conversion error.

The whole history output is as follows:

Setting up configuration variables [SQLSTATE 01000] (Message 50000) Now starting diagnostic analysis [SQLSTATE 01000] (Message 50000) Capturing first pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000) Beginning investigatory queries [SQLSTATE 01000] (Message 50000) Finished running investigatory queries [SQLSTATE 01000] (Message 50000) Waiting to match @Seconds parameter [SQLSTATE 01000] (Message 50000) Capturing second pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000) Analyzing changes between first and second passes of DMVs [SQLSTATE 01000] (Message 50000) Analysis finished, outputting results [SQLSTATE 01000] (Message 50000) Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241). The step failed.

Anyone have any suggestions to what might cause this? Have the output tables changed somehow since the procedure was called sp_Askbrent? The version of sp_Askbrent was reported this way: sp_AskBrent (TM) v20 as of Jan 1 2016 12:00AM.

Any help appreciated.

4

When we renamed it sp_BlitzFirst and went open source, we made several breaking changes, one of which was changing the history table's DATETIME field to be DATETIMEOFFSET instead:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/288

I would use a new history table name, and then it'll get automatically created with the right datatype.

  • Thx that did the trick. Just a quick question regarding the CheckDate: I see that it is originally based on SYSDATETIMEOFFSET(), but before inserting to CheckDate it is converted via nvarchar so that it ends up in the UTC+0 timezone. Is this intentional for checking data from different servers in different timezones? Would it not be easier if it was left in the SYSDATETIMEOFFSET(), and then people who needed to check from different sources could switch to one timezone for comparison? (I do not have much experience with the datetimeoffset, so forgive me if I speak nonsense now). – GHauan Sep 13 '16 at 18:56
  • @GHauan honestly, I don't have much experience with it either! That might totally be a bug. If so, can you file a bug on it in the repo? Thanks. – Brent Ozar Sep 13 '16 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.