June 19, 2002 at 9:52 am
Hello everyone, I got a weird one here:
I used DTS to load a delimited text file into a table. It was loaded fine, I queried the table, took a look at the data, verified the data, etc. However, after sometime(I am not sure how long), there was no data in the table anymore. I queried the table to see how many rows are there, and there was zero. So I loaded the table again using the same process. And again the same result. What is going on? How can I commit the load? Thanks.
June 19, 2002 at 4:57 pm
If you query the load in the middle of the process the records may be available to query but if an error occurrs it will rollback the changes, if after then something else is deleteing. The best workaround is to do the file in small pieces or find the records that is the problem.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 5:07 pm
There is a check box on th Tranform Data Task (Assuming you used this) that specifies the load size of the transaction. Set it to 1 and see if this helps, but as Antares mentioned, you probably have an error in the load somewhere.
Steve Jones
June 19, 2002 at 6:08 pm
I have queried the loaded table way after the table was loaded (or at least way after when DTS said that the load was finished). The second time I loaded this table, I queried it after 2 or more hours had passed by.
June 19, 2002 at 6:21 pm
Ok then I suggest running Profiler to trace the transactions that occurr after the load until you cannot find your data. Something has to be issuing a truncate table or delete. Profiler will be you best bet to catch what it is.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 8:33 pm
Damn!
This morning (around 9:00 am) I loaded the table again, checked it periodically all day to see if the data will be deleted. It is now 7:30 pm and the data has been deleted. Last time I checked it was around 6 pm, so sometime between 6 and 7:30 the data was deleted/truncated. Is this weird or what?
June 19, 2002 at 11:45 pm
Check to see if your system has any schedule jobs in the time you specified which truncates your data.
you should also follow Antares suggestion and have a look at profiler to track what is happening on your db.
HTH
June 20, 2002 at 9:53 am
Or put a delete trigger that rollsback any deletion. That will tell you if it's a delete table or a truncate.
Steve Jones
June 20, 2002 at 10:20 am
Thanks a lot for your help guys. I loaded the table several time last night and sometimes the data would not be there right after DTS was finished loading. The last time I loaded was about 9:40pm and right now I ran a query and the data is still there (it is 9:15 am now). The data could disappear from 1 second after DTS is finished to many hours after DTS is finished. I already checked the jobs and DTS procedures to see if there are any jobs/procedures that might delete/trucate the data...there is nothing. My manager and I started several SQL Profiler sessions and found nothing. Is there a way in SQL Profiler to see which SYSTEM process(s) are deleting from a table?
I shall put a delete trigger on the table and see what happens. I'll update you about my findings. Thanks.
June 20, 2002 at 10:35 am
I need your help on something. I know how to create a trigger, but I wanted to capture some important information such as who (users/system process) is deleting my table. How do I capture such information and put it into a temporar table? Thank you.
June 20, 2002 at 10:39 am
user_name or suser_name() will help. Create a table to store the info in and insert this along with getdate() in a delete trigger.
Steve Jones
June 20, 2002 at 4:42 pm
However delete triggers do not fire if the query is truncate table. This is why I suggest use profiler so it cannot get away and you see the exact query. Also you know it will happen so profiler only has to be run until it occurrs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 21, 2002 at 10:22 am
Just to update everyone on the situation, I am running three different Profiler sessions checking on different things and I have also created a delete trigger. Since yesterday morning, the data has not been deleted/truncated. Will update everyone once the status changes.
June 27, 2002 at 12:07 pm
It sounds like a mystery. Maybe someone was joking with you, but stopped when knew you started the investigation.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply