This will name Ignite SQL hashes that have not been named yet, based on the object (or batch) they are within.
Runs on MS-SQL2005+ (uses VARCHAR(MAX) to concatenate SQL sections).
Run this script in ignite_repository or dpa_repository database (but if you do not, it will just error and exit without doing anything).
@DOIT = 0 by default so no changes will be made; change @DOIT = 1 to actually add the names.
It works best if you have a shorthand code for the object (or batch), with no spaces in it.
Place a comment like this near the beginning of the batch (at the end of the CREATE line of the object):
CREATE PROCEDURE [schema].[procname] -- Ignite=ShortObjCode VersionCode
Place a comment like this within or directly after each statement: -- Ignite=ShortObjCode ShortStmtCode
The hash for the statement will then be named "ShortObjCode ShortStmtCode VersionCode" as long as
Ignite stored the entire object script (or batch), which it might not if it was very long.
But it will work without such comments, by using the first 18 chars of the Object name.
YYMMDD and the last 4 digits of the hash are used, if necessary, to enforce uniqueness.
The last result set will be the SQL Text that still could not be named (not in an object, and no such comment).
If you are unable to inject such comments into the SQL, you may be able to see a pattern in the SQL
which would enable you to synthesize an 18-char "object name" yourself. Search for "synthesize" below to see where.
The last column of both result sets is a link to the Historical Chart for that SQL, so you can check its usage.
(That link is built assuming Ignite is running on localhost, but you can just replace localhost below.)
Free to use, developed by tomkellerconsulting.com 14 Sep 2012.
Submitted to Confio Support 13 March 2013. Submitted to sqlservercentral.com 25 Jan 2014.
Updated 28 Feb 2014 to use LEFT(NEWID(), 4) -- random
instead of RIGHT(CONVERT(VARCHAR(20), TS.H), 4) -- last 4 digits of the hash
to avoid duplicates in large sets of un-named hashes. Also added "double-check not duplicate" to final insert.
Also set @DOIT = 0 by default so no changes will be made unless requested, and select Note if no names inserted.
Updated 17 Feb 2015 to handle 0 SS and -1 SE (specified statement start and end, replace with 1 and sql text length).
Also mention dpa_repository as altenative to ignite_repository since Confio Ignite is now SolarWinds DPA.
Also specify all column names in selects, so that STMT_Specified_Statement and ST_SQL_Text can be XML links.