Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 587 | Views in the last 30 days: 7
 
Related Articles
BLOG

Confio Ignite: Part II

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

FORUM

Batch file

Batch file -- running sql files as a batch

ARTICLE

Invisible Comments

I ran into a dilemma when I was told that I should not allow potential competitors to view my JavaSc...

FORUM

ignite

I'm not sure how to create a graph that collects the following information? [quote] I would li...

FORUM

Management Studio SQL editor

batch commenting

Tags
confio    
database performance analyzer    
dpa    
fuzzy match    
ignite    
performance tuning    
search    
solarwinds    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones