Importing access into sql2005 - schema issue

  • Juanita

    SSCertifiable

    Points: 5300

    HI,

    I'm learning to use sql2005 and working with the import wizard. I have a new database and all tables are created with a schema called ex: MYSCHEMA. The schema is not DBO

    I am in the wizard and get to the part where I select 'copy one or more tables' . When I chose this, I have hundreds of tables to import and the wizard is showing all the DESTINATION tables with the schema DBO not MYSCHEMA. Now if click on the drop down window in the destination box, I can select the table with MYSCHEMA.

    What I want to know is do I have to hand select the correct table for all 360 tables? Is there a place where I can specify that I want all the tables with MYSCHEMA as the schema? :w00t:

    Thank you,

    Juanita

  • Johan Bijnens

    SSC Guru

    Points: 134304

    If you login to sqlserver sql as a non sysadmin, you should be able to define the wanted schema as default schema for that account.

    btw MS has published a tool Migration assistant for access to facilitate upgrades from ms access to sql2005 / 2008. You can download it free.

    Johan


    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[/url] but most of the time this is me

  • Juanita

    SSCertifiable

    Points: 5300

    HI,

    Actually I am logged in as a non sysadmin. And the default schema is 'myschema'... but still shows all destination files as schema DBO.

    Do you have a link for the Migration tool?

    Thank you so much,

    Juanita

  • Johan Bijnens

    SSC Guru

    Points: 134304

    SQL Server Migration Assistant for Access

    Brief Description

    Automates database migration from Access to SQL Server 2005.

    http://www.microsoft.com/downloads/details.aspx?FamilyId=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en

    Johan


    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[/url] but most of the time this is me

  • Juanita

    SSCertifiable

    Points: 5300

    Thanks a bunch!

    Juanita

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

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