Wednesday, March 28, 2012

Migrating database table data from 2005 to 2000

I'm trying to get a database from SQL2005 to SQL2000. I already recreated the database in SQL2005 and I'm just trying to get some data from just a few tables.

It doesn't appear the DTS or any export functionality is available in my version of SQL Express how do I do this. I've tried installing everything I can find.

I've also tried BCP but I keep getting these errors:

SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This is a LOCAL database and I have tried -T for trusted, I have also tried a username and password combination. The database is set to Allow Remote connections. Nothing is working.

I'm typing this

bcp somedb..countries out countries.csv -c -t , -S machine/SQLdatabase -T -U myusername

|||Moving to the SQL Server Express forum.|||

Your options are to (a) script out the tables, run those scripts in the SQL 2005 Express sever, and then use the Import/Export Wizard in SQL 2005 Express to move the data, (b) Check out some of the excellent third party tools.

Comparison Tools
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare
DTS Comparison
Red Gate – DTS Package Compare
Server Comparison
Quest - ServerCompare
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition


That would just get me to the same place I'm already at.

I don't have your step 3 this-->

Import/Export Wizard in SQL 2005 Express

These options do not appear in my SQL Server Management Studio Express. In fact, if they did, I'd be done.


In Object Explorer, right click on your database, select [Tasks], then [Import].

Oops, forget the above, my mind was running on empty.

Try the DTSWizard:

{install drive}:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe


Arnie: I don't got it. I have done a search for DTS* and there is no such file on my computer. I don't have import/export under task. I installed some of the extra tools but apparently not all and you can't rerun the installation program...its too stupid. You can only rerun it from add/remove SQL in the CP, and then its looking for a .msi file but Microsoft distributes the tools as an .EXE. Around and around you go....whoever wrote the installation for SQL Express/2005 should be fired...but only have he/she is beaten profusely about the head out back.

I have Express and apparently there is no SSIS available and no DTS avilable. I've been around this a 100 different ways. I only have 3 tables here....I just want to get bcp running and move on back to SQL 2000. It looks like Microsoft is now charging for the developer tools for SQL 2005 I guess..

I don't have a firewall running. Why does BCP error out? I'm at a loss.


First, verify that [Allow Remote Connections] is checked in SQL Server Configuration Manager.

Second, the DTSWizard is available in the 'toolkit' for download here:


No comments:

Post a Comment