Move data from SQL server 2000 to 2005

Posted on 2008-11-01
Medium Priority
Last Modified: 2008-11-02
I'm trying to copy a set of tables from a sql server 2000 db to a 2005 db.  I've been using enterprise manager but it wont connect to the 2005 db.  I've got MS SQL server management studio express but I dont see the import/export function like in EM.  Any Ideas?
Question by:webdork
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 17

Expert Comment

ID: 22859811
In SQL Server Management Studio, Right Click on the database, select Tasks, Import Data. This will open the wizard for you.
LVL 17

Expert Comment

ID: 22859818
Also, from EM you can Right Click on the database, select All Tasks, Export Data and this will open a DTS Wizard that will allow you to select a 2005 db as your destination.

Author Comment

ID: 22859820
i have sql server management studio express
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

LVL 17

Expert Comment

ID: 22859823
Sorry, forgot that Express does not use SSIS services. Either use the Enterprise Manager Export or the 2005 BCP utility. Good luck!
LVL 13

Expert Comment

ID: 22859832
you also have an option of Database publishing tool

Download this freel tool by Microsoft for publishing Data

in Wizard if you just want to generate data insert statements
set the following options

uncheck Script all object
Schema qualify= False
Types of data to publish =Data only

end result is data script which you can execute on new Database

The tool can also generate scripts for other objects in the database ie. Tables

Author Comment

ID: 22860999
I am trying to move the data through Enterprise Manager Export and get stopped with the following message.

the specified schema name does not exist or you do not have permission to use it
LVL 17

Accepted Solution

HoggZilla earned 2000 total points
ID: 22861472
Test this: Go to SSMS express and create a new table. Can you create a table in the dbo schema? Then, in the EM Export wizard, when you get to the place to pick your destination object - check the schema.tablename, if you can, view the create table stmt. I am not logged in right now so I cant remember the exact details. Bottom line, make sure you are trying to create a destination table.
If you cant figure it out from here, please post full details of the error message and screen shots. We will get it fixed for you. Thanks.

Author Comment

ID: 22861511
Hi and thanks,

I've got it figured out.  I created a DTS package in EM and manually removed all the schema and replaced with dbo.  It seems to be working ok.  One thing, when I edit tables in SSMS Express i get a message about may not save changes you are not logged in as database owner or sa.  The changes are saved, but I'd like to get rid of the irritation message. Any idea?

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question