I have created an SSIS package to run sp_blitz in my production servers. The stored procedure runs fine for servers having a version higher than 2012 but fails for 2008 and 2005.
Please find the subsequent error message that I received while running the package.
[SP Call ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Incorrect syntax near 'RESULT'.".
I have googled about the error and found that EXECUTE WITH RESULT SETS feature was introduced in 2012. Thus running the stored procedure with RESULT SETS fails in lower versions. I am using the below query to execute the stored procedure.
EXEC sp_Blitz WITH RESULT SETS ( ( Priority TINYINT , FindingsGroup VARCHAR(50), Finding VARCHAR(200), DatabaseName NVARCHAR(128), URL VARCHAR(200), Details NVARCHAR(4000), QueryPlan XML NULL, QueryPlanFiltered [NVARCHAR](MAX) NULL, CheckID INT, Server_Name sql_variant, [Server_Version] varchar(128), [Server_Edition] sql_variant, [Server_Product_Level] sql_variant, [Server_Product_Version] sql_variant ));
Half of my production servers are 2008 and 2005. Is there any alternative command or method that can be used so that this can run in lower versions as well.