Post reply

Duplicate tables...

  • July 17, 2006 at 3:23 am

    #62550

    Hi,

    We use SQL Server 2000 as the database behind our application. This just holds indexed tables, nothing fancy at all.

    When the database is created the owner of each table is set to dbo by default. Last week we migrated a client from Informix to SQL Server. The load program that they run once a week tries to create the necessary database tables before hand. In Informix if they exist the program just carries on and does nothing. In SQL Server the program created another set of tables with the owner set to the user who ran the program. So, all the data was loaded into these new tables instead of the original dbo ones. Looking at the database you see duplicated tables with different owners. Something that I never thought would be possible in any database.

    Is there any way to stop SQL Server from doing this?

    Thanks,

    Carl

  • July 17, 2006 at 4:37 am

    #649489

    Hi Carl,

    When you create the tables you can create them by explicitly stating that you want the dbo user to be its owner. So, if you want to create a table called Customer you'd do:

    create table dbo.Customer(.........)

    If you don't qualify the owner then SQL Server will automatically qualify the owner with the user that is running the statement. Unless that user is a sysadmin, in which case the owner defaults to dbo.

    Karl
    source control for SQL Server

  • July 17, 2006 at 4:41 am

    #649492

    Carl, this isn't uncommon with 3rd-party apps.

    You need to ensure that the load program always runs from the same account, otherwise you are going to create multiple copies of each table which will get very messy indeed.

    SQLServer has no problem with duplicate table names (or any object name) as long as the object owner is different for each instance of that object. Strictly, to identify an object, you should use the 3-part naming convention of:

    database_name.object_owner.object_name

    eg. db1.dbo.EmployeeTable

    This is a completely different object to:

    db1.fred.EmployeeTable

    Whenever you create tables yourself, if you're logged into the database as an administrator or as dbo, then all objects will be owned by "dbo".

    When your load program runs, it creates tables owned by the user who ran the load program.

    I suggest that you set your load process up as a scheduled task, either running automatically or manually on-demand. This way, the job will always run from the same account and will begin to behave as you expected it to originally - if tables already exist then the program will carry on.

    Phil

  • July 17, 2006 at 5:23 am

    #649494

    Thanks for the help guys!

    Carl

  • June 1, 2007 at 12:05 pm

    #710408

    I have these duplicate tables as well. Now that I have them, how do I fix this? My duplicates are in a test database that was created from a restored Production DB.

    1. Would it be easier for me to just restore production again?

    2. If I didn't want to start from scratch with a restore from Production, how would I go about getting rid of the duplicate tables?

  • June 4, 2007 at 2:19 pm

    #710707

    It's going to depend on the state of the tables... are the dbo.xxx tables empty? 

    If so, then drop the dbo tables and change ownership on the dups.  If both sets of tables have data, then you will need to update the  dbo.xxx tables with the data from the dups, then drop the dups.

     

     

    Meredith Ryan Smith

  • June 4, 2007 at 11:48 pm

    #710786

    Since you're running an application overauthorized ....

    If you make the application-service-user database owner, it will create objects in the dbo-schema by default.

    Alter the existing tables to the schema dbo

     exec sp_changeobjectowner 'myApplicationUser.AlarmDefinition','dbo'

    or drop them if allowed

    Then change the database owner ...

    exec sp_changedbowner @loginame = 'myApplicationUser' ,@map = 'true'

    (Making myApplicationUser member of db_owners does not cause the behaviour of creating objects in the dbo-schema by default)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply