SQL 2000 Data Transfer is a Snap with SQL 2005

(Page 1 of 7 )

If you want to transfer data between SQL 2000 and SQL 2005, this article explains how. Thanks to the SQL Server Import Export Wizard, it might even be easier than you think.

Introduction

Data Transformation Services in SQL 2000 was an excellent tool. I am using the past tense because the same service in SQL 2005 has all the good features of DTS and then some. This illustrated tutorial looks at transferring data (table or tables) from SQL 2005 back and forth to SQL 2000 from a server on the network. The basic tool, the SQL Server Import Export Wizard, is used to make this transfer. You will see how easy it is to transfer from one to the other. I recommend readers of this article to review my several articles on DTS on the ASP Free site to appreciate the differences.

Summary of tutorial

• The source locations of the data
• Registering the networked SQL 2000 Server
• Exporting from SQL 2005 to SQL 2000 server
• Modifying the transport
• Exporting from SQL 2000 to SQL 2005 server
• Where is the package?
• Summary

The source locations of the data

The MS SQL 2000 server is on a Windows XP Professional machine which is connected through a Cisco router to the MS SQL 2005 Server on a machine with Microsoft XP Professional Media Center edition as the operating system. The Windows-integrated authentication is used for SQL 2005, and SQL authentication is used for the SQL 2000 server. A Northwind database table will be exported to the SQL 2000 Server's database from SQL 2005, and the "pubs" database tables are transferred from SQL 2000 to the SQL 2005. The SQL Server Import Export wizard on SQL 2005 Server is used.

For both export operations, the new data provider SQL Native Client is used. SQL NC, short for SQL Native Client, is a new player in the data access arena. It is not a part of the MDAC suite of data access components, of which there are many versions. SQL NC is supported by SQLXMLOleDb 4.0 and is ideally suited to work with SQL 2005.

Next: Registering the networked SQL 2000 Server >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: SQL 2000 Data Transfer is a Snap with SQL 2005

· This is a transition phase when you move out from SQL 2000 to SQL 2005 and this...   >>> Post your comment now!