hi,

I have peculiar but interesting problem.

I have a DTS Package which is transforming data from a view to a table.

The view that is used in the source is nested up to six levels.
It is similar to the below.

Assuming the 6 views as v1 , v1 ...v6

---View 1 definition ------

select a , b , ...... from tbl a inner join v2 on....

----View 2 definition-----

select a,b,...... from tbl a inner join v3 on ...

---view 3 definition---

select v3.a , v3.b ..... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...

... and the join is until for v6 and atleast 3 columns for eash view.

Views v4 , v5 , v6 are selecting atleast 6 coulmns from different tables and also from table a used in v1 and v2.


The DTS package is scheduled as job.There are other steps too in the package and this DTS runs at the third step.The job runs successfully some times but hangs at the third step at times and there is no clue why it hangs.
But when the job is cancelled and rerun after hanging , it runns successfully the second time and there are no issues the second time.

A trace was run and there is no information of dead locks and time outs on the trace , there is no information on the errorlog for dead locks.

But i presume the issue is with locks but have no proof for the same.

At the time when the job hangs there are number of context ids for the spid that runs the job.

When i queried for locks , i found all the locks for the above mentioned tables and views are Sch - S locks and with GRANT status execpt for one lock which was Sch - M with a WAIT status

But I am not sure why there is Sch - M when there is no change in the schema and the views are just doing a select.

And the peculiar thing is job is running successfully when it is run the secon time.

As i am using only views I am not able to insert into any table to audit the process and check the place of issue.

Please provide any inputs on how to identify the issue.

Regards
Venkat