SQL compare command line gives error while it works with UI

Nehal.Shah

Hi,

I am evaluating ‘command line’ facility in SQL compare tool.
SQL compare version is ‘8.1.2.44’ and dbs are created on SQL server 2008.
This I mainly require to automate my routine steps to synchronize production db with staging db

Problem I am facing:
On my local server I have 2 dbs test1 and test2.(They are identical).
From ‘test2’ I have deleted couple of sp’s.
I have created following command for synchronization.
“sqlcompare /db1:test1 /db2:test2 /exclude:additional /synchronize”

But when I try to synchronize, I get error
“Error: Synchronization of '(local).test1' and '(local).test2' failed: Invalid object name 'BIWMAIN2000SQL..MemberContact'.”
BIWmain2000sql is a separate db and deleted sp has nothing to do with it.
When I try to synchronize the same thing through “Synchronization Wizard” from the tool i.e. UI, it works fine and make changes on test2.
I tried many permutation and combination for command line but no success.

Then I tried using project parameter through command line, it worked fine without any error.
But my limitation is I cannot update objects which are not present in source.
i.e. /exclude:additional

Can anyone tell me why above error is displayed and how it can be removed.

If anything is not clear do let me know.

Regards
Nehal

Michelle T

Usually that error is displayed because something that you are trying to synchronize is referring to the object in question - it's being returned from the SQL Server when you try to run the script.

I suspect the most likely cause of the different behaviour between command line and UI is that you have different options set up in the project. Check what options you have selected in the project (in the Edit Project dialog) and try setting them explicitly in the command line?

Nehal.Shah

Hi Michelle,

(1) I tried by setting up of all possible combination of options in command line but no success.
Note: In project -> Options i have only default options.
(2) In UI i removed all checks in Project -> Options and tried to synch. and it worked. But the same thing is not workin in cmd even by setting /Options:n.
(3) How it is working through command line when i pass project parameter thats also a question? Here it is going to use default options but it does not work in cmd when /options:default is used.
(4) Note: For testing i have retored 2 test db i.e. test1 and test2 from the same back up. Then i have just removed one sp from test2. But things are not working for this. Very strange.
(5) Note: For cmd line, SQL compare is showing correct results but problem happens only while synchronisation.

I think above information is enough to explain the problem i am facing.
Appriciate concrete and prompt reply.

Regards
Nehal

Chris Auckland

I though I would update this post in case anybody else finds a similar problem.

There is a bug in SQL Compare 8 that causes a command line comparison to include identical objects in the script. This is where it differes from the UI as the UI knows to filter out the identical objects.

All that happens when the identical objects are included in the script is that sp_refreshview will be scripted for each view in the schema.

The bug reference for this issue is SC-4542 and is scheduled to be fixed in the next minor release.

As a workaround for this issue, you can add /exclude:Identical to the command to remove the sp_refreshview statements.