view hangs when called thro dts | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

view hangs when called thro dts

Hi, I have a job which calls a DTS package
This DTS package runs a view and inserts the result to a table The job step which calls the DTS package hangs occasionally. If the view is run in query analyzer at this time, it executes without any issues If the job is stopped and started again, then it works fine No other process is blocking this process and no orphaned transactions exists When the job hangs,I see a number of threads (around 50) for this process, out of which around 10 threads are showing as ‘runnable#%92 and others as ‘sleeping#%92 Structure of the view v1
Create view v1 as Select * from v2 inner join table1….. Structure of view V2
———————- Create view v2 as Select * from V3 inner join v4 ….. In short there are nested views up to four levels Any pointers will be helpful
Can you see a error in the job. if so please paste the error
also Check the permmison of the agent user. —————————————-

CHeck the DTS package log about where it is hanging and using PROFILER during that operation might give some clues. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.