What are the challenges you face when working across database platforms? Take the survey
Options

What do do with different object owners

someprogrammersomeprogrammer Posts: 2
edited December 15, 2005 11:03PM in SQL Compare Previous Versions
The stored procedures in one database are owned by dbo, in the other by my user name with the ISP. How can I compare them?

ie.

[dbo].GetEverything

is showing up as a seperate stored procedure from

[mylogin].GetEverything

This is causing the whole thing to basically not work for this situation. Anybody have the solution?

Thanks!

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    SQL Compare does not support comparing SQL Server objects with different ownership in this current version.

    You can change the object ownership to match the other database using sp_changeobjectowner and compare the objects with SQL Compare. If the ownership is hard-coded into the creation DDL for the stored procedure (ie CREATE PROC [owner].[object] rather than CREATE PROC [object]), then there should be no effect when the script runs because it will be created with the proper ownership anyway. This could depend on the rights of the account that you connect to the SQL Server with, though, and you may get an error message instead.
  • Options
    I have the same problem.

    It would be great if a function could be added that enables the user to ignore the owner information when comparing and creating tables (and other objects).

    Elja
  • Options
    For others with the same problem; my solution was to let SQL Compare generate the script, but not run it.
    Instead I saved the script to a file which I opened with a text editor.
    I deleted all of the references to [mylogin]. and saved the script (replace function in text editor recommended :wink: ).

    Next; run the script in the database you want to update.
    This works fine.

    Ofcourse this doesn't help with the comparing itself, but in my case it help alot.

    Since this solution is simple, I hope this feature will be build in SQL Compare soon. :wink:.
    Would save some time..

    Elja
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Elja!

    We are working on the ability to compare and synchronize different objects. If our plans turn out to be feasable, we should have this feature by mid 2006.
  • Options
    To get around the issue where SQL Compare doesn't compare two tables that are the same on two databases (like local and production), but are different according to SQL Compare because the owners are different (i.e. [dbo].[tblCompany] and [abc].[tblCompany]) I do the following:

    Script the production database using SQL Enterprise Manager to a file on your local machine. Open the scripted file, find/replace all instances of [abc] and change it to [dbo]. Then, on the development database server, create a new database and run the script you just made. Now, you have two databases on your development machine:

    dbApplication
    dbApplication_live

    both have the owner [dbo] on all the objects. Run SQL Compare between the two databases and you'll get what you're looking for.

    I hope this helps ANYBODY out there. Boy, lot's of people are going to be upgrading when they come out with a new release! :)
This discussion has been closed.