SQL Server Performance Forum – Threads Archive
DTS caching retrieved data?
I’ve done alot of searching over the last couple of days to find an answer for this problem but I have found nothing. Hopefully someone here has an answer! I have a DTS package (SQL Server 2000 – sp3/sp3a) which runs a stored procedure. The stored procedure queries a table for records that were inserted the day before and returns them to the DTS package as a recordset. I then iterate through the recordset to create a .csv file which is then emailed off to different people. The first day the DTS package ran (I scheduled it to run every morning at 8 am) it worked fine. However, every day since then it is sending out the same data. It is like the DTS package is caching the rowset. I know the stored procedure is working fine because when I run it in SQL Query Analyzer, it brings back the correct data. The first couple of days, I made some changes to the data retrieval method (switched it to query a view and then switched it to use straight SQL). Again, the first time it ran I got the expected results but then everytime after that it would return the same results, again like the returned rowset is being cached. I have confirmed that the ‘Turn on cache’ option is not checked (although I’m not even sure if that is an issue). Also, the file that is sent out everyday is named differently so I know its not just sending out the same file – it sends out a new file with the same data. Does anyone have any ideas? Thanks in advance!]]>