Solution for dependency support

Oct 29, 2009 at 6:58 PM
Edited Oct 29, 2009 at 7:10 PM

Hi All, I'm doing migration to Azure too, here is the snippet for dependency support without try-and-catch ;) , just add the following code to your ScriptWizard.cs

 

       // replace your method GetSmoObjects with the following snippet
        private SqlSmoObject[] GetSmoObjects(CheckedListBox clb)
        {
            SqlSmoObject[] smoObjects = null;
            if (clb.CheckedItems.Count > 0)
            {
                smoObjects = new SqlSmoObject[clb.CheckedItems.Count];
                clb.CheckedItems.CopyTo(smoObjects, 0);
            }
            List<SqlSmoObject> objlist = Sort(selectedDatabase, smoObjects);
            return objlist.ToArray();
        }

        //Add the following method for sorting the dependencies
  private List<SqlSmoObject> Sort(Database sourceDB, SqlSmoObject[] smoObjects) { DependencyWalker dw = new DependencyWalker(sourceDB.Parent); DependencyTree dt = new DependencyTree(dw.DiscoverDependencies(smoObjects, true)); List<SqlSmoObject> sortedsmoObjects = new List<SqlSmoObject>(); foreach (DependencyCollectionNode d in dw.WalkDependencies(dt)) { string name = d.Urn.GetNameForType(d.Urn.Type); SqlSmoObject obj = null; switch (d.Urn.Type) { case "StoredProcedure": obj = sourceDB.StoredProcedures[name]; break; case "Table": obj = sourceDB.Tables[name]; break; case "View": obj = sourceDB.Views[name]; break; } if (obj != null && smoObjects.Count(s => s == obj) > 0) sortedsmoObjects.Add(obj); } return sortedsmoObjects; }

 

 

btw I find the tool quite useful, great job ghuey!

 

Coordinator
Oct 31, 2009 at 3:58 PM

Hi,

Thanks for the code.  I had played with DependencyWalker when I first wrote SQLAzureMW and found that it does not sort just the tables that I want, but gets all of the dependencies so if I pass in 15 tables to sort, it will return 20 tables along with UDT, Stored Procs, etc....  I wanted more control so I ended up not using it.  What I did do is when I upload to SQL Azure, if I get an error because a dependency is missing, I just put it in a retry queue to try later.  What I don't have is a retry queue of the retry queue.  If you find that this is a real issue, then I can figure something out.  If you (or anybody) has a schema that causes SQLAzureMW an issue and can send it to me so I can recreate the issue, then it will make it a lot easier for me to fix the wizard.  I really appreciate you taking the time to pass me the code.  I keep it in my back pocket and see if I can come up with a solution.

Thanks!!!

Oct 31, 2009 at 4:19 PM
Edited Oct 31, 2009 at 4:28 PM

----

Oct 31, 2009 at 4:20 PM
Edited Oct 31, 2009 at 4:28 PM

Hi ghuey,

I got the error that you mentioned, which always returned UDT, Stored Procs, but the trick that I apply is only return those tables in the list(using linq).

 

 

 if (obj != null && smoObjects.Count(s => s == obj) > 0) sortedsmoObjects.Add(obj);

 

I don't not have any issue with try-and-catch, however I have few pretty large tables, which make try-and-catch very expensive for me.

btw happy halloween ;)

Coordinator
Oct 31, 2009 at 8:26 PM

Happy Halloween as well :-)

Something else that I ran into.   In this section of the code:

string name = d.Urn.GetNameForType(d.Urn.Type);

It returns the table name, but it does not return the schema.  So, for example in AdventureWorks, the tables have names like HumanResources.Employee or Person.Address.   Thus when I get to this part of the code:

obj = sourceDB.Tables[name];

obj is always a null because it does not find a match with just the last part of the table name (i.e. Employee vs. HumanResources.Employee).  Any thoughts on how to get the full name?

Thanks

Coordinator
Nov 1, 2009 at 8:34 PM

As I sit here with a stomach full of candy, I came up with something that I think will work.  At least my initial test seem to work.  Here is the code that I added:
 
        private SqlSmoObject[] Sort(Database sourceDB, SqlSmoObject[] smoObjects)
        {
            DependencyWalker dw = new DependencyWalker(sourceDB.Parent);
            DependencyTree dt = dw.DiscoverDependencies(smoObjects, true);
            List<SqlSmoObject> sortedsmoObjects = new List<SqlSmoObject>();
 
            foreach (DependencyCollectionNode d in dw.WalkDependencies(dt))
            {
                SqlSmoObject obj = null;
                foreach (SqlSmoObject sso in smoObjects)
                {
                    if (sso.Urn == d.Urn)
                    {
                        obj = sso;
                        break;
                    }
                }
                if (obj != null) sortedsmoObjects.Add(obj);
            }
            return sortedsmoObjects.ToArray();
        }
 
I still have some other test that I want to run through, but so far, this seems to work pretty well. 

Thanks for your help,
George

Coordinator
Nov 2, 2009 at 2:07 AM

Ok, I ran out of test and shipped v1.8 with the sorting code.  Check it out and see if it works for you.

Thanks!!!