September 20, 2002 at 9:56 am
I need to purge 3 related history tables on regular basis. I want to extract the data to flat file for each table seperately before deletion. I will be selecting and deleting based on unique field common on all tables.
I tried it by selecting max(unique field) minus desirable number of records and storted into a table and executed 3 export jobs to do extraction and deletion of records less than to the above selection from the 3 tables in question. I am getting invalid pointer error in the export job.
Please tell me what is the best way to extract and delete the data from 3 tables.
Thanks
Ashok
ASHOK
September 20, 2002 at 10:36 am
go to aspsqlserver2000@aspfriends.com - or try the sql programming forum here...you can probably get your question answered quicker there perhaps? just a suggestion...
hth,
Michael
Michael Weiss
Michael Weiss
September 21, 2002 at 12:15 pm
Sorry for sending you off to another site yesterday, I wasn't fully understanding your question and thought it was a query related issue only. Anyway, I just finished writing out the instructions you need to accomplish what you want but something got messed up and it didn't post and now is gone. It was quite lengthy and I don't have time to reproduce it now but will do so this evening. Just wanted to let you know so you could be expecting it...
Regards,
Michael
Michael Weiss
Michael Weiss
September 21, 2002 at 6:52 pm
Do the tables need to be joined? By your description, it is difficult for me to be sure. I would do something like:
1. Create DTS Package
2. Add SQL Server Connection object
3. Add Text Destination object
4. In workflow between SQL Server connection and text connection, enter your query to copy records...something like 'SELECT field1, field2, etc FROM table1 WHERE unique_id = ?'
5. Pass in a global variable value for the sql parameter (you can use an input box in an activex task object to set the value of this variable)
6. Add an execute sql task to the package and enter your delete sql statement...something like 'DELETE FROM table1 WHERE unique_id = ?' and pass in your global variable value.
7. Add additional text destination connections and execute sql tasks for each of the other two tables. You can use the same sql server connection if you do not want to run any of these workflows in parallel...
8. Set the package to run as a job.
hth,
Michael
Michael Weiss
Michael Weiss
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply