Schema Compare "missing" several database objects

Question

Hello,

I have been using VS2010 Premium to maintain a database on 4 different machines all running Server 2008 R2 with SQLServer 2008.  We develop Stored Procedures, functions, views, and so on, on our Dev machine.  Then we use the schema compare to write the changes to the other machines.  We have been doing it this way for about a year.

Last week I went to push some changes out, and schema compare showed that over 70 procedures were no longer on the source machine.  So I tried the schema compare with all 4 machines, one other source machine showed the same behavior.  So 2 machines lost procedures and 2 didn't. 

I can see the missing procedures on all 4 machines in server explorer in VS2010, but the compare doesn't show them.  I have checked premissioning on all machines and they appear to be identical.  I have used a demo 3rd party tool and it shows all 4 machines as identical.

Where does VS2010 get it's info for schema compare?  Apparently it's not the same place as the explorer and SSMS, because they show the correct procedures.

Any help would be greatly appreciated.

 

Thanks,

Matt

Answer 1

Hi,

 

I am also seeing some very concerning results coming back from the Schema Compare. If I compare the project as source to the DB as target the results show that many stored procedures are missing. When I check the database however they are present.

So I tried dropping and re-creating one of the stored procedures and then going back to VS2010 and re-running the schema compare. This time the results show that the stored procedure that I dropped/created in the project and DB match. But all to other stored procedures are reported as missing.

Is the schema compare preparing a cache that's getting out of sync ???

 

Thanks,

 

Gary

Answer 2

Hello Matt, Gary,

I try to better understand your issue, and do you mean that you first delete some stored procedures in the source schema and then create one schema compare. However, you do not have these deleted stored procedures shown in the schema compare?

If so, please make sure you have not selected the Only compare elements that exists in the source checkbox on the Schema Compare Options dialog. Once you have checked that checkbox, schema compare will only compare these database objects in the source schema. Please see this screen shot:

In addition, Schema Compare is used to show the differences from a target schema and source schema. And it usually synchronizes the target with the source. Please take a look at this article for further information:

http://msdn.microsoft.com/en-us/library/aa833435.aspx

Thanks.

---

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

Answer 3

Vicky,

I have not deleted anything on the source machine.

What is happening is that a compare between the 2 machines is showing that the source is 'missing' several procedures.  I can see that the source has the procedures in the Server Explorer window.  So I know they should be there, but in the compare they are not in the list of procedures on the source machine.  

I hope these Screen shots will help.  Take a look at uspCreatePages.  It shows that it is missing in the source (167.76.15.204) and will be dropped in the target (167.76.15.123).  When I open the Server Explorer for the source (167.76.15.204) you can see uspCreatePages is actually in the source.

 

---

Thanks, Matt

Answer 4

Hello Matthew,

Thanks for your response. And I think I have better understand your question.

And I take a look at your screenshots I find that actually you use the [167.76.15.204.pic.sa] as the source schema in the Schema Compare. However, in the Server Explorer, you connected to [c.167.76.15.204.pic.dbo]. Could you please make sure you are seeing the same database?

In addition, will you have a chance to take a look at it the SSMS?

Thanks.

---

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

Answer 5

Thanks for the insight.

I deleted and re-added the connection, and I still see:

Server explorer : 167.76.15.204.pci.dbo

SQL Compare : 167.76.15.204.pci.sa

I can't seem to get them to match, I don't see where I can change the .sa to .dbo anywhere in the connection settings.  

 

---

Thanks, Matt

Answer 6

Hello Matthew,

Based on your screenshots, I think the uspCreatePages stored procedure indeed exists in the source database. And it should appear in the Schema Compare result window.

If you try the same thing on another machine, will you get the same issue?

Thanks,

---

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

Answer 7

Vicky,

We have tried this on 2 different PCs, and we get the same results.

It is interesting to me that the tables, views, functions, triggers, and user-defined types all look correct.  it is only the stored procedures that seem to be incorrect.

Matt

---

Thanks, Matt

Answer 8

Hello Matt,

Would you please send me one simple project which you are using? I would like to take a look at the settings of your schema compare options. Please send it to me via email: v-vison @ microsoft dot com.

Thanks.

---

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

Answer 9

Matt,

I am having this same issue.  Did you ever get it resolved?  If so can you share the resolution?

Thanks, Nancy

Answer 10

We did not get this resolved.

It was taking too long to run through the diagnostics.  We found several other products and it was cheaper and faster for us to go with one of those.

Thanks,

Matt

---

Thanks, Matt