Need help to automate SQL schema comparison generation diff scripts with Database Projects in VS 2010 using DTE

Question

I'm trying to automate the database schema comparison process, and want to generate the differences between the database project, and the target database (SQL Server 2008). I'm doing this as part of a build workflow (using TFS 2010), with a custom activity.

Our current process is based on doing the actual comparison with DTE automation (it's an unfortunate inheritance), because we couldn't achieve the following in any other way:

• Use a different .vssettings file for each comparison, to separate the diff scripts according to usage (schema - tables, views, sprocs & funcs)
• Use a set of SQLCMDVARS for the comparison
• Run a post-generation custom script "upgrade" (adding if statements to drops and creates, etc.)

Edited with more information: 
Unfortunately, it turns out that the schema compare options aren't getting loaded (requires a manual reset), and the SQL command variables (SQLCMDVAR) aren't getting loaded.

I tried running Data.SchemaCompareSpecifySQLCMDvariables andData.SchemaCompareOptions, but those take no arguments.

If I run it like this:

dteInstance.ExecuteCommand("Data.SchemaCompareSpecifySQLCMDvariables", string.Empty);
dteInstance.ExecuteCommand("Data.SchemaCompareOptions", string.Empty);

The first window gets opened, and requires manual input. I tried using SendKeys.Send(), but the keys didn't get sent to the screen. In the end the operations timed out, and the next DTE call caused a COM exception:

Command "Data.SchemaCompareSpecifySQLCMDvariables" is not available.

Can anybody help me figure out why it isn't working, how to get it to work, and/or a better way to do it?

Answer 1

Hello Assaf,

I am trying to involve someone familiar with this topic to further look at this issue.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hello,

Thank you for your post. We apologize for not replying sooner.  If you still need assistance with this issue, we recommend opening a support case with Microsoft Technical Support.

Sincerely,

Rob Beene

MSDN Community Support

Answer 3

Robert and Vicky,

Can you guys point to a resource (or anything) that can help us with this case. We are trying to implement something similar too.

Thanks.