Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Move data from SQL server 2000 to 2005

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?
0
webdork
Asked:
webdork
  • 4
  • 3
1 Solution
 
HoggZillaCommented:
In SQL Server Management Studio, Right Click on the database, select Tasks, Import Data. This will open the wizard for you.
0
 
HoggZillaCommented:
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.
0
 
webdorkAuthor Commented:
i have sql server management studio express
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HoggZillaCommented:
Sorry, forgot that Express does not use SSIS services. Either use the Enterprise Manager Export or the 2005 BCP utility. Good luck!
http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx 
0
 
sm394Commented:
you also have an option of Database publishing tool

Download this freel tool by Microsoft for publishing Data
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

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

uncheck Script all object
Next
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
0
 
webdorkAuthor Commented:
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
0
 
HoggZillaCommented:
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.
0
 
webdorkAuthor Commented:
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?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now