Solved

Move data from SQL server 2000 to 2005

Posted on 2008-11-01
8
358 Views
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?
0
Comment
Question by:webdork
  • 4
  • 3
8 Comments
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
In SQL Server Management Studio, Right Click on the database, select Tasks, Import Data. This will open the wizard for you.
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
 

Author Comment

by:webdork
Comment Utility
i have sql server management studio express
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 13

Expert Comment

by:sm394
Comment Utility
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
 

Author Comment

by:webdork
Comment Utility
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
Comment Utility
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
 

Author Comment

by:webdork
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now