Sql-server – Error while running sp_blitz in 2008 SQL Server and lower versions

sp-blitzsql serversql-server-2005sql-server-2008-r2

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.

Best Answer

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.

Related Question