Stored Proc Hangs When Called From DTS

[EdwinGene]

We have a stored procedure which hangs when called from DTS. If the stored procedure is called from Query Analyzer, it runs just fine. This does not happen with any other stored procedure. The logins for DTS and Query Analyzer are identical, so it doesn't look as though permissions are an issue. The statement the stored procedure is hanging on is an Insert into a table variable, and yet there are other table variable inserts which take place in the stored procedure BEFORE this particular insert, and they work great.

We've had three DBA's look at this so far, and nobody can figure it out.

Does anyone have any ideas why the stored procedure execution will hang when called from DTS, but finish normally when called from Query Analyzer? If you need more specific info to help answer this question (which you probably will), please ask.

Thanks.

 

[JayKusch]

Can you post the SP that is being executed?


Thanks

J. Kusch

 

[EdwinGene]

Sorry, no. In the first place it is very long. In the second place legal proprietary issues may be involved. Plus, I don't think it would be ethical to expose our code to allow people to just wade through it in hopes of finding something. So it's easiest just to say "no" and avoid those issues, altogether.

What I was hoping for was that someone might have experience with this type of problem, or would have an idea of what might be causing the problem and have a question to ask about configuration and/or DTS or SQL Server properties.

If someone has questions about the stored procedure code, I will answer any questions as completely as I can without, again, exposing the code for general viewing.

Also, since we've walked through the code, and the stored procedure executes perfectly when it is executed from Query Analyzer, we think it is unlikely that the stored procedure code, itself is at fault.

 

[JayKusch]

I fully understand where you are coming from! BUT in your statement on the initial post you said ... The statement the stored procedure is hanging on is an Insert into a table variable, and yet there are other table variable inserts which take place in the stored procedure BEFORE this particular insert, and they work great.

This might be an area we may need to take a look at. Can you post that section, of course dummied up to conceal any propriatary issue, so that we can get an idea of what is going on?




Thanks

J. Kusch

 

[EdwinGene]

Declare
@IntVar1 Int ,
@IntVar2 Int ,
@DteVarFileDate DateTime,
@DteVarStart DateTime,
@DteVarEnd DateTime

Declare
@TableVar1 Table
(
IntCol1 Int ,
IntCol2 Int ,
IntCol3 Int ,
IntCol4 Int ,
ChrCol1 Char(1)
)

Declare
@TableVar2 Table
(
IntCol1 Int,
IntCol2 Int
)

Declare
@TableVar3 Table
(
IntCol1 Int
)

Declare
@TableVar4 Table
(
IntCol1 Int
)

/*
Set Local Variables Here
*/

/*
Insert Into Other Table Variables Here
*/


/*
This is the table variable insert that hangs when the SP is called from DTS, but works perfectly when the SP is called from Query Analyzer
*/

Insert
@TableVar1
(
IntCol1,
IntCol2,
IntCol3,
IntCol4,
ChrCol1
)
Select
IntCol1 = tb1.IntCol1,
IntCol2 = Sum(Case When tb1.IntCol2 <> 0 and tb1.IntCol3 > 0 Then 1 Else 0 End),
IntCol3 = Sum(
Case
When DatePart(day, tb1.DteCol1) <= DatePart(day, @DteVarFileDate)
and
DatePart(month, tb1.DteCol1) = DatePart(month, @DteVarFileDate) Then
tb1.IntCol2
Else
0
End
),
IntCol4 = Sum(tb1.IntCol2),
ChrCol1 = Case When Sum(tb1.IntCol2) = 0 Then 'Y' Else 'N' End
From
dbo.Table1 tb1 with (NoLock)
Inner Join @TableVar2 tv2
on tv2.IntCol1 = tb1.IntCol1
Inner Join @TableVar3 tv3
on tv3.IntCol1 = tb1.IntCol4
Where
tb1.IntCol5 = @IntVar1
and
tb1.IntCol6 = @IntVar2
and
tb1.DteCol1 between @DteVarStart and @DteVarEnd
and
tb1.IntCol7 not in (Select IntCol1 From @TableVar4)
Group by tb1.IntCol1