SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

NameSQLinConfioIgnite (SolarWinds DPA)

By Thomas Keller,

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.

Total article views: 612 | Views in the last 30 days: 2
Related Articles

Microsoft Ignite Big Data Presentations

There were so many good presentations at Microsoft Ignite, all of which can be viewed on-demand.  I ...


Scripts for SQL PowerShell Session at Microsoft Ignite 2017

This is just a quick blog post to share the scripts I used during my sessions at the Ignite conferen...


Microsoft Ignite Announcements

Many product announcements were made this week at Microsoft Ignite, and I wanted to give a quick ove...


Batch file

Batch file -- running sql files as a batch


Confio Ignite: Part II

I’m continuing to evaluate Confio’s Ignite database monitoring tool. I’ve had it collecting data on ...

database performance analyzer    
fuzzy match    
performance tuning