August 15, 2006 at 9:57 am
This is an infuriating problem.
I have used SQL Server 2000 for years and to date have never had a problem in creating a DTS package (using the Import Export Wizard) that will import data froma production database to my local development database - dropping and recreating the tables and importing all the up-to-date data from the production db.
If this same system is possible in SQL Server 2005, can someone please give me a step by step as to how it is done.
I am running SQL2K5 SP1 and the option to Drop and Recreate the tables is greyed out.
The operation fails if I try any other method (as would be expected).
I have tried deleting the rows and allowing identity inserts, but it fails because there are foreign key constraints on the tables in question so the task cannot complete a truncation of the current data.
This is madness.
I cannot find a solution to this online - if anyone knows of a solution, please let me know where I can find it!
I have searched these forums and someone else posted the same problem without a resolution...
Many thanks in advance to anyone who has the answer!
Cheers,
Rob
August 15, 2006 at 10:17 am
Save the package and either add a step to Drop tables first, progressing on completion or modify the Create table step to check for and drop any existing tables prior to creating them.
For one-offs, I'll typically just drop the tables manually and then run the wizard. For items I'll re-use I'll just code in the drop/create scripts. I generally have to do this anyway because the database name gets coded into the scripts and I've got a lot of scripts that I re-use against different databases.
I will agree on the annoyance factor on this one. SSIS is a bunch more powerful, but I've resorted to DTS at times just to get something done quickly. There are times I just can't afford an hour to mess around with something when DTS will do the job in 5 minutes.
-Pete
August 21, 2006 at 10:01 am
I had hoped that it was simply something in the wizard I was overlooking - I guess not!
I haven't tried to use DTS from enterprise manager to move this data about. I've had issues with moving data between databases but that was down to trying to copy data from a 2005 database to a 2000 database in management studio... I'll have to experiment and see what happens.
Thanks for the idea.
I guess I'll have to wait for SP2 and hope Microsoft deem this worthy of actually fixing. It's a major oversight in my eyes... and apparently I'm not the only one.
Cheers,
Rob
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy