How to copy data from SQL 2000 to SQL 7 RRS feed

  • Question

  • I want to copy a table from SQL 2000 to SQL 7, that are connected though internet. Since the SQL 2000 is my web server, I don't want to touch it. Therefore, I try to create a DTS package in SQL 7 to copy the data, but fail. I cannot connect to SQL 2000, actually it is a Small Business Server 2003. However, I could connect the SQL 2000 at another computer with the Enterprise Manager that comes with SBS2003. The Enterprise Manager that comes with SQL 7 cannot register the SQL 2000.

    Could anyone help me, please?

    Will it be solved if I install the lastest MDAC?

    Any T-SQL would work like VB's createobject("ADODB...")? Then I could write a stored procedure to do the copying.


    Thank you.

    Wednesday, December 13, 2006 6:29 AM


  • Hi Argus,

    There's a variety of ways to achieve your goal. Here's one idea:

    - script the desired table on the SQL 2k using the Enterprise Manager

    - use the script to recreate the table at the SQL 7

    - does the table contain identity and/or constraints? You may need to disable those

    - on the SQL box, use BCP to export the data from the table:

    BCP dbname..tablename out myfile.txt -n -T

    - take the myfile.txt and move it to the SQL 7

    - import the datab using BCP again

    BCP dbname..tablename in myfile.txt -n -T

    - create/enable your constraints

    Of course, this procedure assumes that you won't experience compatibility issues with the data, datatypes, etc. This approach is the simplest and most fault-proof, because all data is operated locally and travels between the 2 servers under the form of a file copy (you could even use BCP against a mapped drive, but that may be slower). Except that, BCP is a quite powerful and performing tool.

    You could also try to use the DTS approach - the dtsrun.exe process usually runs on the client machine where the package is started, so you could use your Enterprise Manager client machine. Please be aware that this would generate quite some traffic - from the SQL 2k box => to the client => to the SQL 7.

    You could also try your script approach, though I would not recommend that. You could simply open 2 connections towards the 2 SQL servers, then start fetching from the first and inserting into the second. This would be quite slow. :-)

    One more approach could be to define a linked server on the SQL 7 to the SQL 2k and just use an INSERT-SELECT with the 4-part name of the table. This is actually not a very bad solution, though this would be transactional (and probably time consuming) - for instance, if 1/2 of the records have been copied over and a network error occurs, you would have to wait for the insert so far to rollback.


    Jivko Dobrev - MSFT
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, January 8, 2007 10:17 PM