Red Gate forums :: View topic - GetMigrationSQL per table ignores DropConstraintsAndIndexes
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

GetMigrationSQL per table ignores DropConstraintsAndIndexes

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Mon Jan 21, 2013 2:37 pm    Post subject: GetMigrationSQL per table ignores DropConstraintsAndIndexes Reply with quote

Hi,

I have a follow-up on this thread:
http://apps.red-gate.com/messageboard//viewtopic.php?t=16164
(which was solved, by the way).

I (again) have a table with a unique index and a clustered index, as written in the other thread.
When calling the method "GetMigrationSQL" of the SQLDataCompare.Engine.SqlProvider, I get the migration SQL. I use the option DropConstraintsAndIndexes to let the script include DROP statements for my unique index and clustered index.
This works perfectly.

However, when I call the method "GetMigrationSQL" with a different overload (per table), I do NOT get these DROP statements.

More details about this issue:

The script that was being generated with "GetMigrationSQL" was getting pretty large for an entire database, when using the overload
"objSqlProvider.GetMigrationSQL(objComparisonSession, True)" to get the entire script.

Therefore, I changed my code to loop through all TableDifferences in the ComparisonSession, and then call the method "GetMigrationSQL" with this overload:
objSqlProvider.GetMigrationSQL(objComparisonSession, objTableDifference, True)
But then the DROP statements for the unique index and clustered index aren't included!

Why is this, and what can I do to overcome this?

PS: I also tried using version 10.2.4.113 of RedGate.SQLDataCompare.Engine.dll, but the problem still occurs.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Jan 23, 2013 3:46 pm    Post subject: Reply with quote

You can specify options in many places in the SQL Data Compare engine, so it's important to pass the EngineDataCompareOptions consistently. be sure to pass these options to the ComparisonSession object as well as the SqlProvider option consistently or you will end up with different sets of options for the comparison and the script generation.
Code:
  mappings.Options = new EngineDataCompareOptions(
                                                MappingOptions.Default,
                                                ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                                                SqlOptions.Default);
...
ComparisonSession session=new ComparisonSession();
session.Options=mappings.Options;
SqlProvider provider=new SqlProvider();
provider.Options=mappings.Options;
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Jan 23, 2013 3:48 pm    Post subject: Reply with quote

I do this already.
Please read the other thread so I don't have to repeat all those steps again.

Could you please try the scenario I posted?
You should get the same results.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Jan 23, 2013 3:49 pm    Post subject: Reply with quote

Sorry, your link 404'd.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Jan 23, 2013 3:50 pm    Post subject: Reply with quote

http://www.red-gate.com/MessageBoard/viewtopic.php?t=16164
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Jan 23, 2013 3:51 pm    Post subject: Reply with quote

If I look at http://www.red-gate.com/messageboard/viewtopic.php?t=16164, I get a link to an issue that is apparently not solved.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Jan 23, 2013 3:52 pm    Post subject: Reply with quote

The issue IS solved, when I execute the "GetMigrationSQL" for the entire database at once.

But when trying to get the migration SQL for a single table, it is NOT solved.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Jan 23, 2013 3:53 pm    Post subject: Reply with quote

Did you try the latest SDK patch: ftp://support.red-gate.com/patches/SQL_Comparison_SDK/SQL%20Comparison%20SDK_10.0.0.170.exe
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Jan 23, 2013 3:53 pm    Post subject: Reply with quote

Yes, that was the last line of my first post in this thread:

"PS: I also tried using version 10.2.4.113 of RedGate.SQLDataCompare.Engine.dll, but the problem still occurs."
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Jan 23, 2013 4:32 pm    Post subject: Reply with quote

Thanks, I have logged an issue with the development team ( CSD-173 ).
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Jan 23, 2013 4:33 pm    Post subject: Reply with quote

Many thanks!
I'm new to this: how will they communicate with me? In this forum topic, or directly via e-mail?
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Fri Feb 01, 2013 10:02 am    Post subject: Reply with quote

For everyone who is reading this topic because they run into a similar issue; I got the following response:
Quote:
I have discussed your case with a member of the development team. Apparently, you are using a method in the API that was designed for the UI to display a single object deployment script. It was never designed to deploy that table and as such it does not pay any attention whatsoever to the DropConstraintsAndIndexes option. She gave the following alternative to get the results that you are looking for. I haven't tried it myself, but hopefully it will work for you (she knows what she is talking about)

"The correct way to do the thing they are trying to do is probably to unmap everything but the table they want to generate the script for, generate the whole script for the database with only that table mapped, then change the mappings and generate another whole-database script for the next table like that."

I'm now going to update my code to use the suggested approach.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group