Drop objects option

Feb 9, 2010 at 9:57 PM

Maybe I missed it, but was thinking it would be helpful to have a "drop destination objects first" option... thoughts?

Coordinator
Feb 10, 2010 at 6:00 PM

Hi,

No, you didn't miss anything, I just don't have it in.  I will put that on my todo list and see what I can come up with.

Thanks for the suggestion,
George

Coordinator
Feb 11, 2010 at 12:47 PM

Hi,

Check out SQLAzureMW v3.1.6.  I created an option called "Script DROP and CREATE".  In the SQLAzureMW.exe.config file look for the key "ScriptDropCreate".  By default, it is set to "Script CREATE".  You can change this to "Script DROP and CREATE" or just "Script DROP".  I tried it out by migrating the Northwind database to SQL Azure.  Then, I ran SQLAzureMW a second time on the Northwind database and did a DROP and CREATE on a few specified tables (along with their data).  It worked great.

Check it out and see if this is what you were looking for.  If you find any problems, please let me know!

Thanks,
George

Feb 12, 2010 at 4:56 AM

Great!  Nice work -- I'll try this later today when I pull down my database.  Thanks much for getting this in -- I'll post back after I've tried it a couple of times.

Feb 12, 2010 at 5:38 PM

OK I gave it a try -- trying to copy from SQL Azure to local.  As soon as it started to create the drop scripts, it crashed.  I'll down the source to see if I can isolate the problem...

Feb 12, 2010 at 5:46 PM

Ran from the source code and getting a null reference on line 505 in ScriptDatabase.cs... all of the database objects seem to be passed in (smoObjects is not null) but not sure what the problem is -- perhaps it's b/c I'm going from SQL Azure to local in this case...

        public void ScriptObjectsNoCheck(SqlSmoObject[] smoObjects)
        {
            StringCollection strColl;

    -->        strColl = _scrptr.Script(smoObjects);

Coordinator
Feb 12, 2010 at 8:37 PM

Hmmmm.  I have moved several databases from SQL Server to SQL Azure and from SQL Azure to SQL Server with no problems.  I guess I have two questions for you:
1) Have you tried without the DROP?
2) Would you be willing to send me the TSQL to create your database so I can try to repo it on my machine?

Please contact me through codeplex and we can try to figure this out.

Thanks,
George

Feb 13, 2010 at 12:55 AM

Message sent.  Just to post here in case others reading the thread: this problem only occurs for me when moving both tables and sprocs.  Moving either alone works fine (oddly)...

Coordinator
Feb 13, 2010 at 7:23 PM

It turned out to be a case sensitive comparison issue. I modified to code to ignore case and it solved the problem. Try SQLAzureMW 3.1.7 and see how it goes.

Thanks and Regards,
George

Coordinator
Feb 14, 2010 at 2:07 AM

For the people following this thread, there is still a problem with the DROP of tables.  What this means is that as long as you have all of the dependent tables selected to the main table you want to drop you will be ok.  If you want to drop ONE table that has dependent tables on it, then you will get errors.  A fix is in process.

Regards,
George