Error while running sp_blitz in 2008 SQL Server and lower versions

Question

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 [50]] 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.

Answer 1

If your goal is to log sp_Blitz's output to table, use the @OutputDatabaseName, @OutputSchemaName, and @OutputTableName parameters as described in the documentation. For example, this will write the sp_BlitzOutput to a table named DBAtools.dbo.BlitzResults:

sp_Blitz @OutputDatabaseName = 'DBAtools', 
  @OutputSchemaName = 'dbo', 
  @OutputTableName = 'BlitzResults';

If your goal is to call SQL Server 2008 stored procedures with SSIS, then use the Execute SQL task.

Answer 2

One way you could go about this if you want to influence data types or column names is create an intermediate table to store the result of the stored procedure and then selecting from it.

Something along the lines of

CREATE TABLE #temp (< column definitions like the stored procedure returns >);

INSERT INTO #temp EXEC sp_Blitz;

SELECT < CAST columns to data types you want > AS < alias column names to names you want > 
FROM #temp;