473,241 Members | 1,616 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Transferring databases from MSDE 2000 to SQL 2000

Bob
Trying to transfer databases from MSDE to SQL. When I use SQL
Enterprise Manager - DTS - import or export, it works for the first
database, but then fails for the others. I select the to and from
databases using DTS - Import - 'MS OLE DB Provider for SQL Server',
then select 'Copy objects and data between SQL Servers'. It fails
with the generic error message: 'Copy objects failed', nothing
further. I tried to just copy data, and it doesn't work totally. On
two databases, it said that it copied everything, but when I go in
under Enterprise manager, some of the tables are incomplete, i.e. no
data, table shows up in the list, but if you try to look at it, it
says that it is missing or empty. Also, one table was not even
copied. In the other two cases, I get errors, does not copy all
tables. I get errors, like insert fails, but the column it references
for the table does not even exist in that table. Any suggestions
would be appreciated.

Jan 28 '07 #1
2 2573
Bob (bo***@earthlink.net) writes:
Trying to transfer databases from MSDE to SQL. When I use SQL
Enterprise Manager - DTS - import or export, it works for the first
database, but then fails for the others. I select the to and from
databases using DTS - Import - 'MS OLE DB Provider for SQL Server',
then select 'Copy objects and data between SQL Servers'. It fails
with the generic error message: 'Copy objects failed', nothing
further. I tried to just copy data, and it doesn't work totally. On
two databases, it said that it copied everything, but when I go in
under Enterprise manager, some of the tables are incomplete, i.e. no
data, table shows up in the list, but if you try to look at it, it
says that it is missing or empty. Also, one table was not even
copied. In the other two cases, I get errors, does not copy all
tables. I get errors, like insert fails, but the column it references
for the table does not even exist in that table. Any suggestions
would be appreciated.
I've never used the transfer stuff, so I cannot answer that part. But
why don't you simply do backup and restore? Once you have restored
you will have to tie users back to server logins with sp_change_users_login.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 28 '07 #2
Bob
Because the file structures and names are different. Need to transfer
the databases so that a conversion program can upgrade the tables to
the current version. I.e. in MSDE files start with vam...., in SQL
2000 (latest version of software), start with am... Also several
tables have new fields...

On Jan 28, 2:36 am, Erland Sommarskog <esq...@sommarskog.sewrote:
Bob (b...@earthlink.net) writes:
Trying to transfer databases from MSDE to SQL. When I use SQL
Enterprise Manager - DTS - import or export, it works for the first
database, but then fails for the others. I select the to and from
databases using DTS - Import - 'MS OLE DB Provider for SQL Server',
then select 'Copy objects and data between SQL Servers'. It fails
with the generic error message: 'Copy objects failed', nothing
further. I tried to just copy data, and it doesn't work totally. On
two databases, it said that it copied everything, but when I go in
under Enterprise manager, some of the tables are incomplete, i.e. no
data, table shows up in the list, but if you try to look at it, it
says that it is missing or empty. Also, one table was not even
copied. In the other two cases, I get errors, does not copy all
tables. I get errors, like insert fails, but the column it references
for the table does not even exist in that table. Any suggestions
would be appreciated.I've never used the transfer stuff, so I cannot answer that part. But
why don't you simply do backup and restore? Once you have restored
you will have to tie users back to server logins with sp_change_users_login.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -- Show quoted text -
Jan 29 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Mike N. | last post by:
I am writing a database app which will run under MSDE on the user's systems. Other than limitations as to the number of concurrent users, are there any issues that I need to be aware of when...
1
by: izzy | last post by:
I was wondering if any of you guys can kindly help me in finding all the different versions of MSDE 2000 that came out since it's first release. I expected to find something similar like Sun's...
0
by: Oldgradgreg | last post by:
HI everyone I am running SQL Server 2000 version 8.00.00 set up in client mode and am using the MSDE version 7.00.623 which i had installed of the office 2000 disk. I was going okay for a...
10
by: noname | last post by:
MSDE 2000 Release A installed under windows 2000 pro will not communicate with SQL Server Manager nor MS Access on peer computer. Can someone help? Have set DISABLENETWORKPROTOCOLS=0 at install...
1
by: Diana Finley | last post by:
Hi all, I just got SQL Server 2000, but I already have MSDE installed. Is there any reason I would want to keep MSDE around on my computer if I install SQL Server 2000? I use MS Visual Studio...
1
by: smahler | last post by:
Hi all, Here is my situation. I have an application that uses SQL 2000. The client has a program that uses MSDE. The client MUST have the MSDE version of the Database. What I have done is to...
4
by: Fred Blair | last post by:
What is the best database to use for a small stand-alone PC application? I know Access, but was wondering what it available that is cheaper. Thanks, Fred
1
by: Sebastian Santacroce | last post by:
Is it posible to package MSDE with VB .net so that its part of the installation? If so, is there documentation somewhere? What about packaging the .net framework with the application as well? ...
11
by: HC | last post by:
I posted this in one of the VB forums but I'm starting to think it might be more appropriate to have it here, since it really seems to be a SQL server (MSDE/Express 2005) problem: Hey, all, I...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.