sp_BlitzFirst @AsOf Functionality Not Working – Troubleshooting

sp-blitzsp-blitzfirstsql serversql-server-2012

I'm trying to run the sp_BlitzFirst stored procedure (using the 7/15/2016 version) with the @AsOf parameter, and I'm getting this error:

EXEC sp_BlitzFirst @AsOf = '2015-02-23 18:45', @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults'

Setting up configuration variables
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

The SQL server version is 11.0.6020.0 (X64).

I used the example query from the documentation page and got this. It's choking on the triple ''' when trying to cast @AsOf to a varchar.

I've worked around this for now, but the documentation said to post here about errors thrown!

Best Answer

Unfortunately, compatibility level 90 (SQL Server 2005) is not supported.

From the known issues list at: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

"Only Microsoft-supported versions of SQL Server are supported here - sorry, 2005 and 2000. Some of these may work some of the time on 2005, but no promises, and don't file a support issue when they fail. (For example, we know the output tables won't work on SQL 2005 because one of the output fields is a DATETIMEOFFSET datatype, which isn't available in 2005.)"

Related Solutions

SQL Server – Why Doesn’t Identity_Insert State Rollback?

This is a session setting; there is nothing to roll back. If you had issued SET DATEFORMAT DMY; or SET LANGUAGE FRENCH;, would you expect an error to revert you to MDY or english_us? What if you had SET SHOWPLAN on, would you expect a rollback to stop showing you plans on subsequent queries?

To be fair, the IDENTITY_INSERT case is a little special, since it's the only session setting I can think of off the top of my head that interacts directly with a table. But it's still applicable only to your session. The thinking is probably that you could go on to insert many rows in multiple batches, even after an error has been raised (as long as it hasn't severed the connection).

As an aside, you're using SQL Server 2012, so why are you using SQL Server 7.0 error handling techniques? You could use TRY/CATCH instead of SET XACT_ABORT and then turn the setting off in the CATCH if it's on (which you can check). For a good primer on error handling, see Erland Sommarskog's articles on Error and Transaction Handling in SQL Server:

SQL Server – How to Convert VARCHAR to DATETIME Format

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)

Best Answer

Related Solutions

SQL Server – Why Doesn’t Identity_Insert State Rollback?

SQL Server – How to Convert VARCHAR to DATETIME Format

Related Question