I would like to create a weekly to monthly job to execute sp_BlitzIndex during the night and insert the results into a table so that I can consume the results.
Here's what I've tried:
INSERT INTO dbo.[IndexBlitzResults]
([PK_IndexBlitzID]
,[Priority]
,[Finding]
,[DatabaseName]
,[Details]
,[Definition]
,[SecretColumns]
,[Usage]
,[Size]
,[MoreInfo]
,[URL]
,[CreateTSQL])
exec [ozar].[sp_BlitzIndex]
@Mode = 0,
@GetAllDatabases = 1,
@BringThePain = 1
And here's the error that it throws:
Starting run. sp_BlitzIndex(TM) v5.4 - June 03, 2017 Create temp tables. Adding UQ index on #IndexSanity (database_id, object_id, index_id)
redacted_db Inserting data into #IndexColumns for clustered indexes and heaps Failure populating temp tables. Last @dsql: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT 29, s.name,
si.object_id, si.index_id, sc.key_ordinal, sc.is_included_column, sc.is_descending_key, sc.partition_ordinal, c.name as column_name, st.name as system_type_name, c.max_length, c.[precision], c.[scale], c.collation_name, c.is_nullable, c.is_identity, c.is_computed, c.is_replicated, c.is_sparse, c.is_filestream, CAST(ic.seed_value AS BIGINT), CAST(ic.increment_value AS INT), CAST(ic.last_value AS BIGINT), ic.is_not_for_replication FROM [redacted_db].sys.indexes si JOIN [redacted_db].sys.columns c ON si.object_id=c.object_id LEFT JOIN [redacted_db].sys.index_columns sc ON sc.object_id = si.object_id and sc.index_id=si.index_id AND sc.column_id=c.column_id LEFT JOIN [redacted_db].sys.identity_columns ic ON c.object_id=ic.object_id and c.column_id=ic.column_id JOIN [redacted_db].sys.types st ON c.system_type_id=st.system_type_id AND c.user_type_id=st.user_type_id JOIN [redacted_db].sys.objects AS so ON si.object_id = so.object_id AND so.is_ms_shipped = 0 JOIN [redacted_db].sys.schemas AS s ON s.schema_id = so.schema_id WHERE si.index_id in (0,1) OPTION (RECOMPILE);Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1427 [Batch Start Line 2] redacted_db database failed to process. An INSERT EXEC statement cannot be nested. Msg 3915, Level 16, State 0, Procedure sp_BlitzIndex, Line 1431 [Batch Start Line 2] Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
I looked into using openrowset, but from what I could tell you can't pass parameters to the stored procedure.
So, how can I insert the results of this stored procedure into the table?