SQL Agent job no longer works with SQL 2022

[sfoulk]

Hello I am running v12 of sp_whoisactive on SQL 2022 CU6 which we just migrated to last weekend. Before this we where on SQL 2016 and my job worked just fine. Now I am getting job failures for the whoisactive job every time it is run through the agent. As soon as the job runs I get: Warning: The join order has been enforced because a local join hint is used. Warning: Null value is eliminated by an aggregate or other SET operation. Warning: The join order has been enforced because a local join hint is used. Warning: The join order has been enforced because a local join hint is used. Warning: Null value is eliminated by an aggregate or other SET operation.

Which fails the job. I have tried downgrading to 11.35 and that works for the most part. But about every third or fourth run of the job I get the same issue which triggers a ticket in our ticketing system. For now I have had to abandon the job until I can come up with a fix. One thought I had was because its SQL 2022 and I dont see where the current stored proc says it works with this version. Any help would be appreciated.

[sfoulk]

This is the tsql for my job: SET NOCOUNT ON;

DECLARE @retention INT = 7, @destination_table VARCHAR(500) = 'WhoIsActive', @destination_database sysname = 'DBAdb', @schema VARCHAR(MAX), @SQL NVARCHAR(4000), @parameters NVARCHAR(500), @exists BIT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table IF OBJECT_ID(@destination_table) IS NULL BEGIN; EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @return_schema = 1, @schema = @schema OUTPUT; SET @schema = REPLACE(@schema, '', @destination_table); EXEC ( @schema ); END;

--create index on collection_time SET @SQL = 'USE ' + QUOTENAME(@destination_database)

• '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0'; SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT'; EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0 BEGIN; SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)'; EXEC ( @SQL ); END;

--collect activity into logging table EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = @destination_table;

--purge older data SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))

• ', GETDATE());'; EXEC ( @SQL );

[erikdarlingdata]

@sfoulk you're a little off here, I think. Those messages show up on any execution of sp_WhoIsActive, and they won't cause your job to fail:

You're going to want to dig into the Agent Job history:

And then expand any failures to find the actual job error. It won't always be in the top-level item here:

I definitely have a test job that runs and logs Who Is Active to a table working on SQL Server 2022, so it's not an issue with the procedure itself.

If you copied the job from another server, it might be an issue with the account that owns the job, a database not existing, or something in the job configuration (schedule, etc.) causing the failing error.

[sfoulk]

THank you for the response. I dont know how I missed it but you are right when it fails the message is: Conversion failed when converting the nvarchar value ' 1)' It was buried in the warnings that I posted above. This seems to happen periodically and there does not appear to be any rhyme or reason for it. I even tried different versions of the SP as some said to upgrade and some said to downgrade.

[erikdarlingdata]

Alright, cool. You might want to add some print statements to all that dynamic SQL to figure out which one it's coming from. I'm gonna close this out for now, since it looks like it's local to the code you found on the internet and not a bug in Who Is Active. Thanks!