This project has moved. For the latest updates, please go here.

Multi layered View Dependencies

Jun 24, 2015 at 7:12 PM
After running the analyzer it lists all views as tables. I am mainly focused on reports. What I would like to see is a reports dependencies, if a dependent is a view, I would like to see the next layer down, the base tables used in the view, this could be multilayered. Is this possible? I looked at doing it on my own but since the table/view reference vary it is not easy (some have brackets, some have schema, some have DB, etc (reports were inherited)) Being able to see all the way down would be a real help to understand what a report is actually using. Is this possible with this tool?
Thanks
Coordinator
Jun 25, 2015 at 5:41 AM
Yes, this is possible.
You need to analyse the database(s) that the reports use, as well as the reports.
The first pass without the database(s) will tell you which databases and servers are in use, which you can then add to the Databases tab in the Dependency Executor.
Make sure that you tick the Enumerate Databases tick box.

This will then capture the dependencies that the views have, as well as stored procedure and function dependencies.

It will still list the views as tables, as there is no way of knowing that a view is a view from the query in a report, or a view, or a stored procedure etc.

Keith
Jun 25, 2015 at 3:35 PM
Keith,
First off I'm excited it should work but I am running into an error in trying those steps. I ran the analysis on the report server and everything seemed to work fine. Now I am going back to enumerate the databases and I am getting an error. I restarted, did some google searches, searched CodePlex, and nothing on the message. Any ideas what the issue is? I tried integrated security and an sql account. Its not a DB permissions issue. (I am not sure where 'Account' in the error message is coming from as that is not the DB, user, etc.)

Enumerating Database metadata for Server...<my info here>
Enumerate the Tables and their Dependencies
Could not enumerate the database: Syntax error: Missing operand after 'Account' operator.
Committing analysis information to database...Completed.

Thanks
Jun 25, 2015 at 3:37 PM
Clicked 'Analyse 2008' one more time and this error message popped up at the end this time.

Unhandled Exception: System.InvalidOperationException: Cannot read keys when either application does not have a console or when console input has been redirected from a file. Try Console.Read.
at System.Console.ReadKey(Boolean intercept)
at Microsoft.Samples.DependencyAnalyzer.Program.Main(String[] args)
Jun 25, 2015 at 4:05 PM
I apologize if it seems like I am spamming here but I am posting as I work through it. It seems like the issue was a table name (This setup is something I inherited which is why I am trying to figure out the dependencies). There was a table named dbo.'Account View$' and those single quotes were in the table name, so I think your app bombed when I hit that table. I took the quotes off the table and everything started running correctly and I am getting the view dependencies you talked about. Thank you for your help.
Jun 25, 2015 at 5:03 PM
Keith,
Well after getting that to work I ran into a problem with the view dependencies. A little back ground. There is one instance with multiple databases, there is a reporting database that the reports connect to. The views I was getting dependencies for use cross database references (3 and 4 part names, Not linked servers) to pull data together from a number of databases. These databases often repeat table names across them.

So I wanted to go Report>View>Base table in multiple databases (and however many layers). However, when it builds the report database, all the referenced tables get created as being a table in the reporting database, so while the table name shows up it does not have the correct database path (just dbo), and since the names are used in multiple databases, I cannot determine the path without looking at the views source code in the DB, defeating my end goal. I tried loading the databases in the hierarchy that they are referenced but that did not seem to work.

Am I doing something wrong in how I am loading this that I am not getting the correct references?

Thanks again.
Coordinator
Jun 26, 2015 at 12:59 PM
Hmmm...
The code that generates an "Identifier" checks to see how many parts the name is made up of, and it checks for 4, 3 ,2, and 1 part names. So it should be returning the 4 part name as the table name. eg. [Server].[Database].[Schema],[Object]
The difficulty is that, the table will be assigned to the ConnectionID, which means that you will get a database, with 4 part names in it, which are actually in a different database.

I'll have to build a test scenario and some reports and scan it, to see what is actually happening.
Keith

ps.
And add sql 2014 and add sql 2016.
This project has been on the back burner for a while.
Coordinator
Jun 27, 2015 at 3:32 AM
More Digging...
The code that parses a database, and finds all the dependencies, assumes that all objects in that database are referencing back to itself.
objectName = "[" + parentNode.Urn.GetAttribute("Schema") + "].[" + parentNode.Urn.GetAttribute("Name") + "]";
// snip
parentName = "[" + walkNode.Urn.GetAttribute("Schema") + "].[" + walkNode.Urn.GetAttribute("Name") + "]";
objectName is fine, as it really is in the database, but parentName is a problem, when you have cross database/server views, as it's dropping the 1st two parts of the Urn.

Because of this issue, the current version will not discover the cross database dependencies.

I'm moving this to the Issues section.
Coordinator
Jun 27, 2015 at 3:33 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jun 27, 2015 at 12:54 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jun 27, 2015 at 12:55 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jun 30, 2015 at 1:52 PM
All issues above have been addressed in Alpha 16.
Please download and let me know how it goes.
Keith
Jun 30, 2015 at 2:06 PM
Downloading now. Will try and test in the next two days. Thanks
Jun 30, 2015 at 2:50 PM
Keith, did a quick test and noticed two things.
1) While it is not picking up different databases all the time, it is some times. If a 3 part name is used in a report it is showing up in the viewer. If the process is report > view > table, the table at layer three is not showing the database name (just dbo). I am not sure if it is that I did not enumerate the database in the proper order or not. I loaded all the reports, then did all the databases. (I reran a third time with everything again but nothing changed)
2)While it is syntactically correct in the DB I find it is horrible coding standards and your application is not handling it. The person who built this system used a double dot (..) notation instead of entering a schema. So they wrote dbname1..tablename instead of dbname1.dbo.tablename. The database interprets double dots to be the default schema. I would never do this unfortunately the person before me used this a lot and your systems does not like it. This is not the problem with issue number one as they used the proper 3 part name in the cases I reviewed. I just wanted to mention this.
Thanks
Coordinator
Jul 1, 2015 at 12:25 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jul 1, 2015 at 1:02 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jul 1, 2015 at 1:07 PM
These will be fixed in the next release.
I've fixed one, but the other I'm still working on.
The report scan showing a three part name is actually wrong. It should be showing a 2 part name, and updating the connection string.
This is so that it will show you the reports that are being used for a table, when you start from the base table, that is then referenced by the view, which is then referenced by the report.
I'm considering adding a new command line option, to force three part names into the display.
Keith
Jul 1, 2015 at 1:20 PM
I retested this morning and confirmed my previous issue with it not getting 3 parts names for the base table in a view (report>view>table) when I scanned everything at once in the first pass.

Sounds good and thank you for your work on this!
Coordinator
Jul 6, 2015 at 8:42 AM
This should now be addressed (Alpha 17 release).
I've added an option to force three part names from all scanners (Reports, Database, SSIS and SSAS).
Marked as answer by kmartin on 7/6/2015 at 12:42 AM
Jul 6, 2015 at 11:55 AM

Thanks. I will check as soon as i can but that will be end of week.

Jul 13, 2015 at 6:17 PM
Sorry for the delay. I am getting an error on scanning reports. (Also note, if you have an old version you must remove and install fresh for the new version to run)
The DB scan seemed to run fine and all entries have three parts names under the relational databases section. The error is when it was trying to scan the report server. It scanned one report and then failed.

Error occurred: Could not load file or assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
Committing analysis information to database...Completed.

Please advise and thanks for your work.
Coordinator
Jul 14, 2015 at 12:26 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jul 17, 2015 at 2:05 PM
Alpha 18 has been released, which fixes the missing TransactSql DLL.
Alpha 15 -> Alpha 18 will auto upgrade the installed files. Preceding versions were using a different installer technology, and requires the uninstall. If this proves not to be the case, please raise an Issue.
Marked as answer by kmartin on 7/17/2015 at 6:05 AM
Jul 17, 2015 at 6:16 PM
I can see how gloriously messed up the environment I inherited is! Thank you! This will help a tremendous amount with analysis and tracking. It will take me some time to digest this information.

I still had to remove and install to get this to work, just FYI.

One other things that would be a cool feature is the ability to save the configuration information. By this I mean the repo database location, databases to be enumerated, the report server info, etc. Since we have a number of databases I have to enter every time I want to rescan. This is just a thought, no need to rush into anything.

Seriously, thanks for your work on this. I'd recommend this application to anyone operating in Sql server environment.