Updating sp_Blitz with sp_BlitzUpdate

Updating utility stored procedures can be painful.  To help keep your sp_Blitz script up to date, we’ve built a simple cloud-based update system: sp_BlitzUpdate.

sp_BlitzUpdate fetches the latest version of sp_Blitz from the cloud and patches it for you.

It works by calling the OPENROWSET command to open a connection from your SQL Server to our public SQL Server in Amazon EC2.  It’s kind of like setting up a one-time linked server to the cloud, but without the hassles.  You can see an example in action by running this script:

SELECT a.ScriptDDL
FROM OPENROWSET('SQLNCLI', 'Server=publicsql.sqlservertroubleshooting.com;UID=ReadOnly;PWD=PainRelief;',
'SELECT TOP 1 ScriptDDL
FROM dbo.Scripts
WHERE ScriptName = ''sp_Blitz''
ORDER BY ScriptID DESC') AS a;

That fetches the stored procedure definition from our server.  sp_BlitzUpdate sets up a variable, populates the variable by fetching the stored proc definition from the cloud, and then executes the string.

Permissions Required: Ad Hoc Distributed Queries

This trick requires the Ad Hoc Distributed Queries option to be turned on – but it’s not turned on by default.  To enable it, run the following commands:

EXECUTE SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE SP_CONFIGURE 'Ad Hoc Distributed Queries', '1';
RECONFIGURE WITH OVERRIDE;
GO

Some shops see this as a security risk because developers can use OPENROWSET to open connections from SQL Server to somewhere else.  They could use this to read files or connect to public SQL Servers like ours.

If you’d rather not update your sp_Blitz over the web, that’s totally okay – you can always download it manually.