Drop and recreate table not an option
-
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
-
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
-
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply