Compare wrongly indicates tables do not exist on one server

Hans Van EylenHans Van Eylen Posts: 9
edited July 19, 2005 11:52AM in SQL Compare Previous Versions
I compared a database on server A and B and successfully ran the script generated by SQL Compare on B to make it equal to A.

When I now compare again, SQL Compare indicates that three views do not exist on server B. However, they do exist. Running the synchronization script on B gives the obvious error message : There is already an object named xyz.

Version 3.2.0.5 (just updated to this version)

Hans

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello Hans,

    This wouldn't possibly have anything to do with running the script on a case-sensitive database, would it?
  • Hello Brian,

    no, the databases are not case-sensitive.

    Hans
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello Hans,

    What if the objects that already exist in the other database are not views?

    Can you try:
    select ID, xtype from sysobjects where [name]='xyz'
    

    The XTYPE column will tell you the type of object (see BOL for more information). For instance, U is a table.
  • Hello Brian,

    when I ran the compare I assumed the SQL Compare did indeed find the three objects on server A and not on B.

    When I ran the query on sysobjects however, I found out that they exist on B (as type V) and not on A. Nevertheless, SQL Compare indicates the opposite (blue arrow pointing from A to B) and proposes the wrong script (creating objects on B and deleting them from A).

    I can send you screenshots and scripts if you like. Company policy does not allow me to put them on a public forum.

    thanks

    Hans
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Hans,

    Sure! Can you please send them to [email protected]? XTYPE V is a view, by the way.

    Thanks!
  • Apparently this is a frequently occurring issue :

    the name of the view was adapated with Enterprise Manager in the DDL. As a result the name of the view in the DDL is different from the one in sysobjects.
    The top part of the window uses sysobjects, while the scripts come from the DDL.

    I've suggested that future versions of SQL Compare detect such inconsistencies and warn the user and it already seems to be on their to-do list.

    Thanks to Brian for investigating this together with us.

    Hans
This discussion has been closed.