Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    MSDE upsizing - table skipped (Access 2000 SR1)

    Hi

    I have Access 2000 SR1 and MSDE 1.0 installed under Windows 98 SE. Upsizing an Access 2000 database to MSDE works fine under the sa login acccount in MSDE, or another account when that account is given sysadmin authority in MSDE. No problems there!

    However, all the documentation I have seen implies that upsizing should work for an id with CREATE DATABASE privileges on the server. Yet when I create a new account with dbcreator privileges and run the upsizing wizard, all the tables are skipped - no specific errors (e.g. authority) are given. Confusingly, if I tell the Wizard to upsize the table STRUCTURE only, this will work - the new database and tables are created, and I can then link to the target tables and populate them with a query for example. This suggests that authority is not the issue.

    Has anyone come across this before? I'm practising at home in advance of being let loose on a real SQL Server at work, so I'm trying to iron out all the potential problems with the process this way.

    Thanks

    Jeremy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MSDE upsizing - table skipped (Access 2000 SR1)

    What is your target SQL Server - SQL 7 or SQL 2000? There is a new version of the upsizing wizard that you need to download from the MS web site if you are using SQL 2000, as the one with A2k gives an "Overflow" error. That may also solve some bizzare problems with SQL 7/MSDE if you install it. BTW, on the real SQL Server you will want to have "Integrated" security if possible, which solves many of these issues. If you are going to be doing any serious design work you will want to have dbowner permissions also. And you also will want to make sure any objects (tables, views, stored procedures) belong to dbo. Hope this helps.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: MSDE upsizing - table skipped (Access 2000 SR1)

    The target is MSDE 1.0, i.e. the SQL Server 7.0 equivalent. Because this is just a practice environment running W98SE and Office 2000 and some simple databases built already under MSDE I don't really have the option of installing SQL 2000 Desktop (for one thing I don't have the product nor OXP).

    I was just testing out the claim that upsizing would work with a non-sysadmin id, since I am unlikely to be given sysadmin in a real SQL Server environment. The fact that upsizing table STRUCTURES works for a dbcreator/dbowner id and I can then append to the linked tables with a query gives me one workaround; also the DTS import wizard supplied with Office 2000 is perfectly happy to create AND populate a target table in MSDE using an id that is only dbcreator or dbowner.

    I was just wondering how others on this board have upsized - have they actually been given susadmin authority on a production SQL Server machine, or managed to achieve the transition with a lower authority?

    Jeremy

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MSDE upsizing - table skipped (Access 2000 SR1)

    My experience isn't entirely what you are looking for as I have nearly always had sysadmin capability. In the one case I didn't (SQL 2000 and Access 2000) we had some problems, but basically were able to function fine with db_owner for the database. In that case the DBA had built the database before hand, and we just upsized tables. There are other ways to get data to SQL Server that you will probably want to have in your bag of tricks too. One way is to simply "export" the table to SQL Server. Often that will work when the upsizing wizard fails. Then there are the reverse tools that you can use to import data from Access to SQL Server (you don't get those with the MSDE or Desktop).

    Hopefully others will share their experience using the upsizing wizard.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: MSDE upsizing - table skipped (Access 2000 SR1)

    Thanks Wendell.

    Fortunately the Access 2000/MSDE 1.0 environment DOES come with the DTS import wizard which is working fine for my test purposes. I suspect the situation would be different if I had OXP with SQL 2000 Desktop, although my working environment is going to be Access 2000 + SQL 2000 so it would be dangerous to get set into one way of thinking and then find that didn't work.

    I'm going to experiment with as many different approaches as possible - if I ever get to the bottom of the original problem I'll post on this board.

    Jeremy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •