User-Defined Dependency Tracking

Visualizes SQL Server object dependencies.

User-Defined Dependency Tracking

Postby dhathewa » Thu Sep 16, 2010 6:14 pm

I don't think this is redundant, I checked around the forum and help topics and found no info on this item.

Some of our stored procedures do EXECs on strings to manipulate data. There are usually db objects referenced by these EXECs. Dependency Tracker doesn't track these "invisible" dependencies but we do need to track them.

Is it possible to manually add a "User-Defined" dependency to an object? Then, this item's dependencies could be tracked and the dependencies of items dependent on those items could be automatically added.
dhathewa
 
Posts: 6
Joined: Mon Dec 07, 2009 7:44 pm
Location: Woodbury, MN

Postby David Atkinson » Thu Sep 16, 2010 7:25 pm

Dynamic SQL isn't supported in SQL Dependency Tracker, and there's no way to add a user-defined dependency.

Have you considered using SQL Search to find these types of dependencies?

David Atkinson
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby dhathewa » Thu Sep 16, 2010 10:57 pm

I'm unfamiliar with SQL Search but searching and writing down dependencies... well, I could do almost that well with a couple of queries against sysdepends and a spreadsheet and then searching and dressing my spreadsheet up with manually added entries, no?

Or I could just do the basic dependencies into a spreadsheet, search for EXECs and send that list of stored procs offshore to get a list of further dependencies at $15/hour.

OK... I get that there's no feature in the product to do this, which is a huge disappointment, but there are certainly other dbas out there that have dynamic SQL. "Have you considered using SQL Search?" is a disappointing answer.
dhathewa
 
Posts: 6
Joined: Mon Dec 07, 2009 7:44 pm
Location: Woodbury, MN

Postby David Atkinson » Thu Sep 16, 2010 11:02 pm

Thank you for your feedback. I'm sorry it wasn't the answer you were looking for, but the fact is that this isn't a current capability of the tool, and I would be lying if I said it was coming soon.

Please don't dismiss SQL Search as an option just yet. There's really little reason not to give it a go given that it's a free tool.

http://www.red-gate.com/products/SQL_Search/

Kind regards,

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Workaround?

Postby dhathewa » Wed Sep 29, 2010 8:23 pm

OK... let's try a workaround. For each table, X, that may be referenced by dynamic SQL, I include something like this in the body of the proc:

declare @testfail int
set @testfail = 0
if @testfail = 1 select top 1 * from X where 0 = 1

It seems to provide the dependency tracking information I need but it won't ever return anything and doesn't seem to change the way the proc runs.

Does anyone think there is a disadvantage to this that I've overlooked? There's probably a performance hit but when compared to the performance of assembling and executing a string, I doubt that it's a big deal.
dhathewa
 
Posts: 6
Joined: Mon Dec 07, 2009 7:44 pm
Location: Woodbury, MN


Return to SQL Dependency Tracker 2

Who is online

Users browsing this forum: No registered users and 0 guests