Post reply

Export tables to SQL Server

  • August 25, 2004 at 9:01 am

    #76179

    I'm using Access to export some tables to SQL server.  The problem is that I want to have the tables appear with the dbo as the database owner as opposed to my user name.  In T-SQL I can just call the table dbo.tablename to avoid my user name became the default owner.  Is there a way to accomplish this in Access?  Is there a way once the table in in SQL server to simply re-designated the database owner part of the table name?

    Thanks

    Gregg

  • August 26, 2004 at 5:38 am

    #520677

    You should import the table into sql server from sql server.  Run the DTS-Import wizard, or right-click tables and select import data.  Select Microsoft Access as your data source.

    This will allow you to specifiy any owner for the table ("Destination"), by default it will use [databasename].[dbo].[tablename].  You also have the option to modify the script/transformations.

  • August 26, 2004 at 5:42 am

    #520679

    I forgot to answer the 2nd part to your question.

    "Is there a way once the table in in SQL server to simply re-designated the database owner part of the table name?"

    Select the table in enterprise manager, then design.  From there you can "Manage Relationships" then select the "tables" tab and change the owner from the drop-down list.

     

     

  • August 26, 2004 at 7:19 am

    #520694

    EXEC sp_changeobjectowner 'imported',dbo

     

    This will change the owner. 

  • August 26, 2004 at 7:23 pm

    #520801

    Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

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