Post reply

Newbie help- copy SQL2005 tables to SQL2000

  • March 8, 2006 at 12:10 pm

    #112221

    I'm a developer, quite comfortable with SQL Server 2000 and Enterprise Manager. This week the web hoster upgraded all the databases on a server that hosts two of my clients to SQL Server 2005, and I can no longer administer with Enterprise Manager. Their reply was "Microsoft told us that you could still use Enterprise Manager for administration" but that isn't true, at least how they configured it.

    I've got the SQL2005 client tools and fired up the Integration services - and don't know what the heck I'm doing. Using the SSIS Import and Export Wizard I finally created individual packages for each of my tables. Putting a multi-table copy in a package failed because even though I set each table to Enable Identity Inserts that needed it, it still reset them to 1 and added sequentially rather than copy the value from the source table.

    Also, it looked like you could set up a configuration for the source and destination connections and save them, and then refer to them in the package - but the wizard made me specify the servers, userids and passwords each time.

    I've got two clients with the same tables, different databases. I'd like to be able to clone the package(s) and just swap the Source and Destinations.

    There has GOT to be a better way. Basically I need an exact copy of the data (i.e., delete destination first).

    Can someone point me in the right direction?

  • March 9, 2006 at 7:23 am

    #625676

    If you're having so much trouble with SSIS, why not run a normal dts from the enterprise manager if you're more comfortable with that. I have three dts packages that move data from 2005 to 2000 dbs, no problems. I just make sure that I save the dts packages to the 2000 machine.

  • March 11, 2006 at 1:59 pm

    #626140

    I'm wondering how Microsoft screw this up! DTSWIZ.exe was simple to transfer ojects and data between SQL server.I've tried with there SQL 2005 export and import wizard but this only seems to transfer data. Why did they remove the object transfer from the export/import wizard? The last thing I want to do is create some package just for a one off transfer of objects and data! 

    I'veb tried to use there new SSIS to transfer schema and data and It didn't work! I just received errors using the Transfer Objects. I looked up the error on there site and found nothing... 

    Has anyone found any good documentation on how to transfer data and objects from 2000 --> 2005? using SQL 2005?

    Thanks in advanced...!

    ~GM

  • March 23, 2006 at 9:43 pm

    #628510

    Quite pissed off myself, it's nearly impossible wants to drop the tables because it's creating them without pks, fks, constraints etc... any advice would be great....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply