By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

Transferring databases from MSDE 2000 to SQL 2000

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.